Context Transition - The Whiteboard #03

[@leppyr64]

Rewatching this series after watching the more recent videos. I think the most important detail that has been bothering me is that a measure reference has an implicit calculate. The other is the implicit filter( all ( ) ) when using a filter condition inside of a calculate. I spend so much time with my other power users making them create measures instead of relying on implicit measures in their model design. Imagine their surprise when I start talking about implicit things later. I understand because the longhand of the DAX gets a little wild without these implicit things. Keep up the good work folks!

[@bradj229]

Thanks for explaining with visuals. It helped me understand a lot easier :)

[@mwaltercpa]

Visually seeing each row move into the filter context to evaluate (under filter context) really helps solidify the role of row context within a filter context evaluation. Thanks Marco!

[@sarthaks]

The most important and complex concept in DAX.. This is really helpful

[@richardmartin7904]

Please keep doing these type of videos. Very useful for people like me.

[@ozzhernandez8166]

Fantastic serie!!

[@shafa7668]

Really nice video as usual!

[@rahullamba9318]

So well explained! Thank you!

[@Milhouse77BS]

7:38 Good point on internal optimization on iteration. Didn't know that.

[@samanthapikus3178]

Love your white board explanations. Can you explain what you mean by, we say: Filter Columns and Iterate Columns not Tables?

[@bravucod]

Great explanation!

[@lmblancoancos]

Thank you so much Marco. This video together your book and articles are the best tool to learn about this concept😀

[@OlsoNounas]

At 3:02 you said that the customer table is filtered. Technically, isn't this incorrect? It's the sales table which is filtered and the customer table can only iterate over a filtered version of the sales table, or am I missing something?

[@marcorusso7472]

Good catch! I should have said "the Customer table is filtered by the filter context, BUT because we don't have any filter on Customer columns, then it's iterated entirely". I understand why it's confusing, sorry for that!

[@OlsoNounas]

@@marcorusso7472 thanks for clarifying Marco. Keep up the great work. God bless you guys.

[@Spirovsk]

whats the difference between using distinct versus values ?

[@SQLBI]

See www.sqlbi.com/articles/blank-row-in-dax/

[@TheBaozi]

This is the best explanation of context transition! Thank you so much! I can't wait to see others episodes.😍

[@Bharath_PBI]

Thank you 👍

[@mshparber]

Thanks! Can you please show an example where you use two columns inside a MAXX, two columns from different tables, lets say Product and Country. Here DISTINCT or VALUES will not work. What is recommended to use?

[@marcorusso7472]

You should use an iteration on either SUMMARIZE ( Sales, Product[Name], Customer[Country] ) or KEEPFILTERS ( CROSSJOIN ( DISTINCT ( Product[Name] ), DISTINCT ( Customer[Country] ) ) ). Which one is better... it depends - usually the former, unless Sales is big (100m rows or more) and the dimensions are small. Measuring it is better.

[@mshparber]

@@marcorusso7472 Thanks, Marco!

[@DawoodChoudry-r3j]

sales amount measure you have created is sum(sales(amount)) or just sales(amount) Thanks

[@simon5296]

Thanks Marco for the video. In your example, I understand after context transition, there are 3 filters (year, brand, and max), but remember the sales amount = sumx (sales, price & qty), so the sales amount itself is a sum over many rows. But on top of that, there is a max. Is the max replacing the sum in the sales amount measure? Is the outcome of max a single row's sale amount, or a sum of the sales amount column? thanks a lot!

[@williamarthur4801]

Interesting as ever. I hope you do variables, I'm on that chapter and trying to understand where to put them, I've always placed at very start, but had a situation where I had to declare within Calculate and want to understand 'Why?'. Ottimo libro, ottimo sito, ottimi video. Don't ever drop the catch phrase.

[@SQLBI]

Variables are scheduled: www.sqlbi.com/blog/marco/2022/07/14/the-whiteboard-video-series-on-sqlbi-youtube-channel/

[@liang-tsairlin7053]

Hello, I am wondering if you can help me using DAX to identify the customers who have the max sales in the first matrix (table) cited instead of just the numbers. By the way, great presentations for all 3 whiteboards. Thank you.

[@AjaySingh-hp6eh]

This is really so much complicated. I am thinking about giving up on this whole Power BI thing due to DAX

[@sandeepbarge4699]

How does it understand that it should take max of sales only for those customers that have sales for given brand and product? Because filters on date and product tables are not propagated to customers table. How and when does it filter customers that have sales only for given brand and date? Sorry if I missed something. Please can you clarify?

[@SQLBI]

It does not filter that - but those customers have no sales.

[@1yyymmmddd]

Isn't this explanation simpler: The MAXX function only sees Contoso 2019 rows of the expanded table and calculates max amount for the relevant cell in the visual ?

[@SQLBI]

No, for two reasons. 1) There is no "visual" in DAX, it's Power BI that translates the visual element into DAX syntax parts. 2) MAXX iterates Customer, which has an expanded table identical to Customer. Only Sales has a larger expanded table (that is involved in the context transition if you iterate Sales and then invoke CALCULATE), but this is not the case for this example.

[@coollehavre]

it doesnt work in my sample database : my sales amount are : Total Ventes = SUMX( '💲Ventes', '💲Ventes'[Prix Vente]*'💲Ventes'[Qté] ) and my measure to have the maxx : Max Ventes Commerciaux = MAXX( VALUES('💲Ventes'[Commercial]), [Total Ventes]) but the result is the same than the SUM, not the MAX by month ?

[@SQLBI]

You should iterate months.

[@coollehavre]

@@SQLBI i don't understand that you mean ? that give me the same result . i wrote : Max Ventes Commerciaux = MAXX( VALUES('Calendar'[Mois Année]), [Total Ventes])

[@SQLBI]

Try the sample of the article - if your model provides different results, look at the differences in the data model.

[@coollehavre]

@@SQLBI ok, where can i get the sample ?

[@SQLBI]

It's the standard Contoso model we use in most of SQLBI articles - for example, you can use this one: www.sqlbi.com/articles/variables-in-dax/

[@powerbinareal]

Muito bom!

[@MTawfik86]

the most confusing video I have ever watched :)

[@Nura853]

Thank you Marco! Exellent explanation!👏

[@holahandstrom]

I would create the 'Explicit table' that =MAXX() iterates over. It takes less brain-compute to understand, than an Implicit (conceptual) explanation.

[@QuLaZz]

Maybe it would be better to use Dax studio, to show real data for every step of the calculation. So we can see the Distinct Country, Sales Amount for Contoso and 2019, and then to see the Max value.

[@Wzxxx]

Why Distinct and not Values?

[@sourabhpatel3834]

I am calculating Total sales in two ways but it is providing wrong results First way : Total sales = var sale = SUMX( order_details, order_details[unitPrice] * order_details[quantity]) var discount = SUMX( order_details, order_details[discount] * order_details[unitPrice] * order_details[quantity] ) return sale-discount __________________________________________________________________________________________________ and the second way ( i am using sale variable): Total sales = var sale = SUMX( order_details, order_details[unitPrice] * order_details[quantity]) var discount = SUMX( order_details, order_details[discount] * sale ) return sale-discount this two must provide same result ?

[@SQLBI]

No, the second one use the total value of sale evaluated only once.