How to SIMPLIFY DAX using Power Query 

That's great Barbara. DAX is a difficult concept to understand and apply correctly. We all struggle with it. Power Query can often make it simpler.

Cheers

You're welcome, thanks for taking the time to leave some kind feedback

😀 Cheers!

Definitely recommended best practice to add columns in Power Query ( or the source ) rather than using DAX calculated columns. “As far upstream as possible, as far downstream as necessary” to quote Matthew Roche. Easier to debug and better performance.

You're welcome

You’re welcome

You’re welcome Irene. Simplicity and “debug-ability” are important for me

You’re welcome. Yep, PQ comes to the rescue regularly

Kind words, thank you

Cheers

Thanks Maurice, yes Power Query is so much easier to understand. Sometimes there’s no avoiding complex DAX or Power Query can be misused, bit there’s a nice balance to be searched for in many scenarios.

My pleasure!

Thanks. If you want to know more about relationships and data model then this might help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RV47yX70NN8.htmlsi=Vmu0SB6Xb9LPw-73

@@AccessAnalytic I watched entire video but I didn't see anything I wanted. If the previous table were auxiliar table how I should use it in real dashboard and if it enter as part of relationships or not

@sirasnet6499 - I’m not understanding sorry

You’re welcome.

I go with sacrificing longer refresh for ease of debugging / editing in future and simpler DAX. Better end user and future report owner experience.

Cheers Ken

Hi, note I'm still using DAX to keep it dynamic, and I'm not storing the aggregated data in Power Query. Although aggregating in PQ might be the right approach if your data is unnecessarily granular. I'm also not saying this is the approach for all problems, but it can make things a lot quicker in a big data set too. Might be worth reading this thread... twitter.com/Milhouse/status/1579138159266652161

Absolutely Carlos. Sometimes there’s no avoiding complex DAX but when I can I do

You’re welcome

Not something I've delved into, but technically a returning customer is one where the new customer flag 1 Not sure of the definition of temporary lost.

hope it helps!

Thanks

The total is an interesting one. Question is should it add up the monthly figures or is it a total for year?

@@AccessAnalytic Means as per the selection to have the result

You’re welcome, thanks

Thanks Kebin

Cheers. Depends what the total should show. Number of Monthly visits? Then in Excel you’d need a combination of IF HASONEVALUE and a SUMX

I think the key thing here is that the meausre isn't showing the number of visits by 100 club members in the selected time period, it's showing us the no. of 100 club members that visted in that time period. Therefore the total is correct as it's giving us the no. of 100 club memebers that visited in the year. However my pedantry would probably say we should name the measure something slightly different. :) EDIT: I've just re-watched the video and he uses an accurate and clearer name for the meausre in the example at the begining. See 40 seconds in. btw, I totally agree and enjoy with the example of Roche's Maxim here. I'm a big fan of it and your PQ videos too!

Thanks Paul. oh yeah. Mathew Roche. the purple hair guy. I like his videos too! I will also re-watch Wynns video

Cheers Paul

😁

👍🏼

Thanks Brandon

Excellent! Thanks Nìall