Row Context in DAX
Вставка
- Опубліковано 28 бер 2022
- Understanding the difference between row context and filter context is the first and most important concept to learn to use DAX correctly. This video introduces the row context.
Article and download: sql.bi/746944?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Наука та технологія
Notes for future revision.
Row context exist in:
1. Calculated column. By definition - a column consists of rows, and values are calculated row by row.
Amt = sales[qty] * sales[price]
_Works for creating new column, where Amt is calculated row by row, by default.
2. Measure that include table iteration, FunctionX()
Amt = SUM(sales[qty]) * SUM(sales[price])
_Work correctly only in certain situations e.g. the filter (of the the table or visual) is at the lowest granularity e.g. per row, per day per sale per customer.
_Works also at aggregated level e.g. per month, but it gives wrong result. As it gives Total A of all rows x Total B of all row, instead of Total of "AxB of each row".
So, to calculate Amt correctly,
need to first do sales[qty] * sales[price] row by row,
then sum the results from all rows.
How?
How to first multiply row by row, then sum?
Use an X function that iterates row by row. In this case SUMX.
How to first multiply row by row, then sum?
SUMX (row by row multiplication)
But which row of what table? Specify the table!
SUMX (table_name, row by row multiplication)
Similarly, to get an average of a ratio:
AVERAGEX (table_name, row by row division).
For FUNCTIONX( table_name, expression_formula), just imagine a new column of is created in table_name, with value from expression_formula for every row. Then, FUNCTION is applied to all rows
guys ... this the best channel about DAX i have come across in youtube
Thanks for this video. I've been struggling to understand the difference between measures and calculated columns for some time now, especially as it relates to DAX formulas. This tutorial helped me understand the difference better.
Actually, your explanation requires some effort to understand well about the row context and filter context. But once I got it, it blows my mind. Thanks Alberto. Great video.
We are blessed to have "SQLBI". Thank you!
Thank you for sharing, really great idea to build the understanding layer by layer:
*Row Context
*Filter Context
*CALCULATE Function
*Context Transition
Great.... Now clear about all aggregate function with 'X' and without 'X'
And also understand what is ROW CONTEXT exactly
Thanks a lot!!!
I LOVE his approach: watch the video once, twice, three times ... until it becomes boring - because you know/understand the concept!!!
This is awesome, thank you!
This was a FANTASTIC video on Row Context! I know have a much better understanding of this concept and the roles that an iterator plays. Thank you so much!!!!!!
Nice video, Alberto. It actually is a nice addition to the explanation you gave me about context transition earlier today. It definitely is more clear for me now!
What a wonderful teacher. Beautifully done. Thank you!
Wonderful! That's exactly what I needed to get started with DAX. Thank you very much Alberto for sharing your DAX wisdom.
Great explanation Alberto, thanks. I always thought i knew row context but now i know it better. Bravo!!
@Alberto - Thank you so much for starting this series of videos! Looking forward for further videos.
Very nice explanation. Probably best i have ever watched. Finally! Thank You
The code comment in green explains a lot. Such a niuance
This video is simply great - very well presented and explained
Looking forward to the next video. Very helpful 👍
Thank you Alberto... Thank God it's weekend, I'll consume a lot of your videos today. Lol
Brilliant explanation. Thank you! Please keep doing similar videos.
Very nice tips! Thanks for demonstrating. Thumbs up!!
Great video as always, looking forward to the next one for newbies!
A última medida foi SHOW!!! Mas uma dica sobre contexto. Parabéns!!!!!
Thank you so much for the time that you give to explain us. That is amazing
Thank you so much for this video. You explained it really nicely.
As always, the masters at work!
Wonderful, Awesome explanation 👏
Great. Looking forward for next video.
Ciao Alberto, i am new to Power bi and i really appreciate this video(the First i m going through). It Is extremely useful, thanks so much
Sir, you are the best, period !
Perfect, I keep messing up row/filter context when making dax
Great Video ! Thank you Alberto.
Thank you Alberto, very useful.
Very good, easy to understand now.
that's helpful, and i liked how its explined.
Great video, thank you!
A Great Explanation
Thank you! Great axplanation!
Amazing master class
Another example of why he's one of the GOATs 🐐! 🎉
Thank you, love your explanation! :)
very good job, ottimo lavoro grazie!
Loved it !
Such a great video 👍
Thank you for another amazing video!
I'm wondering if it is correct to say the following (I'm referring to the code of "Amount Col" with 2 Row contexts (min 13:50 of the video).
Because "Amount Col" is a calculated column, it's evaluated in row context, therefore calculation we have in SUMX() is calculated for every row of Sales table. Then SUMX() is introducing another filter context over unfiltered sales table and calculated Sales[Quantity] * Sales[Net Price] again for every row. As a result, if we think about number of iterations, SUMX is calculated 13.915 times (number of rows in Sales table) for each row. There are 13.915 rows in the table, so there are 13.915*13.915 iterations to obtain a final result (which is the same for each row, but however calculated individually for each of them).
Great as always
Very good video! Thank you! :)
thanks very useful as Always
thank you very much for the video!!! :D
Thank you so much Alberto. You have really explained it well after many years of using Power BI , I now understand it so much better
@alberto. At 20:00 you mention „the easiest is the filter context“. But I assume you intended to say row context is the easiest to understand ?
Very nice!
Really great. Wish it was at least 1 hour long.
Great video! Hope there is a Playlist that aggregates this video with other concept videos.
The best way is going through the articles on www.sqlbi.com website - each video is related to an article, there is a better way to connect the videos starting from written content!
You are awesome.
Thanks
Dear sir, good expiation would request please create more video's on power bi !
thank you
great ... as usual
Grazie Alberto for the video.
One question, in the measure "Test", shouldn't
RELATEDTABLE (Sales) be used in the second FILTER function?
I know this is more advanced than the video is aiming for, but:
in the final code with stacked row contexts, does the concept of Expanded Tables factor in at all?
Alberto is the nested SUMX in the end another way of computing something complex from our Fact Table (many side) in our filtering table (1 side) but without calculated column - with column we usually use COUNTROWS.... RELATEDTABLE() but looks to me that Sales[CustomerKey] = Customer[CustomerKey] in the nested sumx could achieve tha same if written correctly
GREAT video (as always). Quick question unrelated to DAX: What tablet are you using to demo at the latter half of the video? I am in the market and have to do a lot of similar demos leveraging MS Whiteboard.
You can find all the details here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/
@@SQLBI Thank you!
How are Dax expression executed? Does the calculation starts from the center out, or the out to in?
Hi , I have different I'd with values on multiple dates. I need to find count of distinct Id that has sum of values for last 5 days is zero . Is there is any possible way for adding calculated value in filter in dax
TOP!
Thank you for this lesson. I have a question - What is the main goal of this DAX?
First it picks the the country USA from Customer, then filters Sales condition and then the final If condition on the Sales condition right?
So what will this Dax produce?
Edit:
Also may I inquire why you used Sumx when you could have used Sum if you wanted to get only the totals. Example: Sum(Filter(
Any reason I am missing for using Sumx.
As he said, this measure was specifically to understand and learn row context, not to be a good, efficient measure.
ahhhh ! as you said "DAX is simple" you start to explain it to " 👌👌 make it easy 👍👍" 🙂
Great as always but one question. In table row context is applied by default, but by using a first argument in function SUMX() we automatically tells table to „ignore row context”? In other words, in physics (-) + (-) = + . Here: Row context + Row context = Scan whole table ? Does this logic make sense?
It doesn’t ignore the row context. For each row, it creates a new row context which scans the entire sales table to give the grand total. So if there are 10,000 rows there will be 10,000 iterations and each one of those does a full scan of the table (another 10,000) rows to produce the same total for each row.
i agree with your confusion, it was really bad explained... i still dont get it. Why the hell is it creating a full scan for every column? Can you give me an example where I need this behaviour?
@@Briefklammer1 when you need grand total for entire table in the current row in a calculated column.
Because result of SUMX (have row contex) is 12.337.640(total number).Calculated column also have row contex, you must calculate this number for each row ,row contex doesnt filter table and pruduced this total number for each row.
That means every iterator function works as a row context when creating a measure. I mean SUMX, AverageX....etc.
Perfetto
please share the series wise index
Wonderful video alberto, so we can say that a row context its in a nutshell, a for loop right?
A row context is generated by an iterator, which is similar to a for loop.
I'm reading now your excelent book = The Definitive Guide to DAX (2nd edition)
where you explain ROW CONTEXT transition
It happens in sitiation when CALCULATE is inside ITERATOR like SUMX ( Ex: SUMX( CALCULATE ) )
The context transitiom mimics ROW CONTEXT trying to to get the same result as ROW CONTEXT using Filter Context
(sometimes , if the rows are NOT unique it can lead to double-rows mistakes)
But there's another situation when ITERATOR like SUMX() is inside CALCULATE ( Ex: CALCULATE(SUMX) )
In the latter case we'd better call it TABLE CONTEXT (as apposes to ROW CONTEXT)
Because cotext transition happens not just for individual iterated row (as in the case with SUMX(CALCULATE ) )
BUT FOR A WHOLE TABLE ! In this case a new termin like "TABLE CONTEXT transition" is more appropriate
Not really. You only have filter context and row context. The context transition transforms the existing row context(s) into equivalent filter(s) in the filter context. Every table function is evaluated in the filter context and returns the rows visible there.
then what happens in situation like CALCULATETABLE(SUMX () )
when iterator is inside CALCULATETABLE?
SUMX creates row context visible in current filter context created by report visuals
But as I understand , there's NO context transition here because CALULATE is outside the row context/ Can you pls explaon this situation?
First of all, many thanks for such great video. I have question, why when you wrote measure as column SUMX(Sales, Sales[Quantity] * Sales[Unite Price]) we get grand total whereas when you write it as measure it works differently(meaning filter context works and it calculates row by row). If anyone can explain it to me I will highly appreciate :)
Review how filter context works - check related videos in the article, or look at other content on www.sqlbi.com
how to create a row context in a measure if the data is text?
Hi. Someone could explain my something?
I wrote the follwing measure to calculate month average.
Geração Mes =
VAR dataAtual = SELECTEDVALUE(dCalendario[Date])
VAR mesAnoAtual = SELECTEDVALUE(dCalendario[Mes Ano])
RETURN
IF(
DAY(dataAtual) = 1,
CALCULATE(
DIVIDE([Geração Diaria], COUNTROWS(dCalendario)),
FILTER(ALL(dCalendario), dCalendario[Mes Ano] = mesAnoAtual)
)
)
I tried to apply a combination between AVERAGEX and SUMX, but I didnot work.
In other hand, I tried to use EARLIER instead of SELECTVALUE. However, this did not work.
I dont have performance isues with this measure, but I really want to understand where I lost myself in the conceptions.
Thanks.
EARLIER work only for row context in nested iterators. You should review how that works and - in general - it is better to use variables rather than EARLIER.
Check this and related articles: dax.guide/earlier/
@@SQLBI Thanks. I just really now that I have not nest iterators.
How come the =SUMX(FILTER(CUSTOMER,CUSTOMER[Country]="USA"),SUMX(FILTER(SALES,SALES[CustomerKey]=[CustomerKey]),SALES[Quantity]*SALES[Unit Price])) GRAND TOTAL is not showing the right figure?
Instead of "USA" try "United States"
@Albero : Even if I use
Amount := SUM ( Sales[Quantity] ) * SUM ( Sales[Net Price] ), I seem to be getting the correct answer in the table visual. I believe this could be potentially due to filter context. So which is better version :
Amount := SUM ( Sales[Quantity] ) * SUM ( Sales[Net Price] ) OR
Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
For anyone having the same question, I quickly realized after posting this comment that your totals would be messed up even if value in individual rows are correct. Also, if you are using card visual, aggregated answer would be different. So depending on what you are trying to do, one of the formula is correct and other is wrong.
Hey Trushit, hope you are doing well.
I had this same problem too and you are right when you put it into Card Visual and the answers are similar but here is where I will say ALMOST similar.
Sum - Sum is the total of one column. In your example, the total of Sales[Quantity] multiplied by the total of Sales[Net Price]. This is all fine and dandy, looks great and you get the answer. So what's the problem?
The problem is this is not what Sales Quantity * Sales Net Price means.
Sumx - Sumx is Sales[Quantity] * Sales[Net Price] individually. Not the total but individually. So if you have 2 columns called Sales[Quantity] and Sales[Net Price] and have 3 rows then Sumx goes to the first row takes Sales[Quantity] number from Sales[Quantity[ column, then takes Sales[Net Price] first number from the Sales[Net Price] Column and multiplies it.
Then it does the same for 2nd and 3rd row it adds it all up and gives the total.
Context:
Key thing to note is context. For example, If your business says we have a data with 10 Quantity and 10 Price and we want to know the total amount then you will use Sumx because you are multiplying every 10 Quality and Price, getting the answer and then getting the total.
Hello community, i think a key idea ,that needs to be noticed, is that a measure can be in one of two shapes , either a single value, or a column of values. And that's why we need to sepecify this thing called context row. A calculated column is always in a shape of a serie of values, thus no need to specify a row context.
What do you think guys?!!🙄
I'm really struggling with understanding what is meant by iteration 😢
SUMX is an iterator: for each row of the table in the first argument, it evaluates the second argument. An iteration is the processing of one row!
Thanks for the video, just in case the Test measure is using "USA" instead of "United States"
Test =
SUMX (
FILTER (
Customer,
Customer[Country] = "United States"
),
SUMX (
FILTER (
Sales,
Sales[CustomerKey] = Customer[CustomerKey] &&
Customer[Age] >= 20
),
IF (
Customer[Age]