Row Context in DAX

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.

[ivangennaro]

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.

[snakeeyesOFFICIAL]

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

[karannchew]

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

[vietndk]

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.

[gborka]

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

[paravej]

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

Thanks a lot!!!

[tacijjola]

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

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

[osPA]

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

[amjadzahid]

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

[subratachrec]

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

[sandeepbarge]

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

[konstantinvolke]

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

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

[joelngige]

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

[michele]

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

[sergiocaptuleio]

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

[LookNumber]

What a wonderful teacher. Beautifully done. Thank you!

[sergiizelenko]

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