Row Context in DAX

[@snakeeyesOFFICIAL76]

guys ... this the best channel about DAX i have come across in youtube

[@ivangennaro5484]

I have been struggling with DAX for quite a long time, but this video was absolutely GOLD. It just cleared my mind, bringing me the basics and fundamentals to understand what's really going on. Thank you.

[@karannchew2534]

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

[@vietndk5437]

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.

[@paravej]

Great.... Now clear about all aggregate function with 'X' and without 'X' And also understand what is ROW CONTEXT exactly Thanks a lot!!!

[@gborka]

Thank you for sharing, really great idea to build the understanding layer by layer: *Row Context *Filter Context *CALCULATE Function *Context Transition

[@osPA78]

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!!!!!!

[@tacijjola]

Wonderful! That's exactly what I needed to get started with DAX. Thank you very much Alberto for sharing your DAX wisdom.

[@amjadzahid]

We are blessed to have "SQLBI". Thank you!

[@sandeepbarge4699]

@Alberto - Thank you so much for starting this series of videos! Looking forward for further videos.

[@MrPelastus]

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.

[@subratachrec]

Absolutely brilliant way of teaching the concept.. appreciate your efforts!!

[@konstantinvolke4569]

@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 ?

[@DararithKim]

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!

[@joelngige5776]

Great explanation Alberto, thanks. I always thought i knew row context but now i know it better. Bravo!!

[@LookNumber9]

What a wonderful teacher. Beautifully done. Thank you!

[@MaestrosounD]

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!

[@scramiro1]

Great video! Hope there is a Playlist that aggregates this video with other concept videos.

[@SQLBI]

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!

[@sergiizelenko4114]

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).

[@michele37276]

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

[@kerryjiang1294]

Looking forward to the next video. Very helpful 👍

[@shashankpandey1019]

Wonderful, Awesome explanation 👏

[@sergiocaptuleio1088]

A última medida foi SHOW!!! Mas uma dica sobre contexto. Parabéns!!!!!

[@tomaszs.4811]

This video is simply great - very well presented and explained

[@timianalytics7150]

Thank you Alberto... Thank God it's weekend, I'll consume a lot of your videos today. Lol

[@wayneedmondson1065]

Very nice tips! Thanks for demonstrating. Thumbs up!!

[@xXPhixiusXx]

Another example of why he's one of the GOATs 🐐! 🎉

[@sandeepkhawas3367]

Dear sir, good expiation would request please create more video's on power bi !

[@livenere]

How are Dax expression executed? Does the calculation starts from the center out, or the out to in?

[@robertkiermasz4513]

Very good, easy to understand now.

[@filipef.6304]

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?

[@PeterKontogeorgis]

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.

[@Briefklammer1]

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?

[@jasKSG]

@@Briefklammer1 when you need grand total for entire table in the current row in a calculated column.

[@thevenom6902]

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.

[@marcofestu]

Grazie Alberto for the video. One question, in the measure "Test", shouldn't RELATEDTABLE (Sales) be used in the second FILTER function?

[@vdweghp]

Sir, you are the best, period !

[@NicholasEnslow]

As always, the masters at work!

[@saharlatifi3510]

Great. Looking forward for next video.

[@AkshayKumar-vd5wn]

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.

[@TRXSTA38]

As he said, this measure was specifically to understand and learn row context, not to be a good, efficient measure.

[@luchoniv]

Thank you Alberto, very useful.

[@dbalkin777]

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.

[@SQLBI]

You can find all the details here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/

[@dbalkin777]

@@SQLBI Thank you!

[@PHILIPROYBENEDICTLOUIS]

Great Video ! Thank you Alberto.

[@atanasprodatanasov9233]

Brilliant explanation. Thank you! Please keep doing similar videos.

[@RyanReynolds89]

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?

[@myroslavamrochko1908]

that's helpful, and i liked how its explined.

[@MrMalorian]

Perfect, I keep messing up row/filter context when making dax

[@jasKSG]

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

[@3pandya]

Thank you so much for this video. You explained it really nicely.

[@ianalvarez7953]

Wonderful video alberto, so we can say that a row context its in a nutshell, a for loop right?

[@SQLBI]

A row context is generated by an iterator, which is similar to a for loop.

[@laurenceabrahams3996]

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

[@brunotesser624]

Great video as always, looking forward to the next one for newbies!

[@anuragkumar-dm1er]

Really great. Wish it was at least 1 hour long.

[@Wzxxx]

Very nice explanation. Probably best i have ever watched. Finally! Thank You The code comment in green explains a lot. Such a niuance

[@raitup00]

Amazing master class

[@niavras]

Thank you! Great axplanation!

[@lukereds9975]

very good job, ottimo lavoro grazie!

[@gilbertosegoviano7701]

Thank you so much for the time that you give to explain us. That is amazing

[@trilokinathsingh5705]

please share the series wise index

[@melvin9993]

Great as always

[@marvinlobo7683]

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

[@ed-0075]

Very good video! Thank you! :)

[@נתןזילברמן]

Great video, thank you!

[@ram_qr]

A Great Explanation

[@FullRewardMinistries]

Anyone else rewatch it 4 times yet? I've spent about 3 hours on that DAX function, but I've almost figured it out!

[@Gaist-zj8tq]

thanks very useful as Always

[@user-ro3lx3yt8p]

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

[@SQLBI]

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.

[@user-ro3lx3yt8p]

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?

[@jgarale]

thank you very much for the video!!! :D

[@zelim9863]

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 :)

[@SQLBI]

Review how filter context works - check related videos in the article, or look at other content on www.sqlbi.com

[@iulianburlacu]

Loved it !

[@louism.4980]

Thank you, love your explanation! :)

[@giorgijsfgsdykTV]

Such a great video 👍

[@MahadiHasan-qk5ko]

That means every iterator function works as a row context when creating a measure. I mean SUMX, AverageX....etc.

[@DIGITAL_COOKING]

ahhhh ! as you said "DAX is simple" you start to explain it to " 👌👌 make it easy 👍👍" 🙂

[@starcrosswongyl]

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?

[@ram_qr]

Instead of "USA" try "United States"

[@pantomima5841]

You are awesome.

[@MarciusMGSouza]

Very nice!

[@MrAbrandao]

how to create a row context in a measure if the data is text?

[@gregoryoliveira8358]

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.

[@SQLBI]

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/

[@gregoryoliveira8358]

@@SQLBI Thanks. I just really now that I have not nest iterators.

[@powerbinareal]

TOP!

[@MrDenisben]

great ... as usual

[@fabiocaetano2535]

Thanks

[@pbihari0214]

thank you

[@YassineLaRegardee]

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?!!🙄

[@1tsvaishnav]

@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] )

[@1tsvaishnav]

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.

[@AkshayKumar-vd5wn]

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.

[@lisamgreenleaf]

I'm really struggling with understanding what is meant by iteration 😢

[@SQLBI]

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!

[@amas2010]

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]

[@oliveroshea5765]

Perfetto

[@AndreaBuselli]

😅