Forum Discussion
Understanding the DAX Measure
Hallo all,
learning power bi und trying to understand how Earlier function works.
here is a small example with a following data set
| ID | Value1 | Value2 |
| 10 | 6 | 20 |
| 11 | 7 | 11 |
| 12 | 8 | 15 |
| 13 | 9 | 21 |
I have applied following DAX Mesure as calculated column.
FLAG =
VAR _value1 = Table1[Value1]
VAR _value2 = Table1[Value2]
VAR Index = Table1[ID]
RETURN
COUNTROWS(
FILTER (
Table1,
(
Table1[Value1]<=_value1 && Table1[Value2] >= _value2
)
&& Table1[ID] <> Index
))
Based on my manual caculation powerbi will go trough following combination
DAx Mesaure Filter will filter alle True values and count the rest so according to me folllowing output has to be there (Manually Calculated )
| ID | Value1 | Value2 | FLAG |
| 10 | 6 | 20 | 1 |
| 11 | 7 | 11 | 3 |
| 12 | 8 | 15 | 3 |
| 13 | 9 | 21 | 3 |
Power Bi gives me following output.
| ID | Value1 | Value2 | FLAG |
| 10 | 6 | 20 | |
| 11 | 7 | 11 | 1 |
| 12 | 8 | 15 | 1 |
| 13 | 9 | 21 | 0 |
I would like to understand how this Measure gives me only on ID 11 and 12 FLAG 1.
please correct my understanding.
I have attached the excel with all combination and also pibx file for your refrence.
Any explanation is greatly appreciated.
Good to see you are digging in to understand DAX with a simple example first. One comment. You used "measure" to describe what you put in a calculated column. While both Measures and Calculated Columns contain DAX expressions, they are not interchangeable. For example, the first variable in your expression (=Table1[Value1]) would return an error in a Measure. Calculated Columns have row context so it knows to get the value on that row (a measure would expect that column to be aggregated somehow).
I would encourage you to learn to write measures wherever possible, and only do Calculated Columns when you need something for the axis/category/legend of a visual, or for performance reasons.
In any case, the FILTER() function will return only rows that return TRUE(). In your case, there are three conditions. To pass all three, the iterated row (in FILTER()) has to
1 - be a different row than the current one (ID <> index)
2 - Value1 has to be less than the stored Value1
and
3 - Value2 has to be greater than the stored Value2
For the 1st row, there are no rows with Value1 <6, so you get blank
For the 2nd row, only ID 10 row meets all 3, so you get a result of 1.
For the 3rd row, only the 2nd row meets all 3, so you get a result of 1
For the 4th row, there are no rows with Value2 >21, so you get blank (not sure why you show 0 there)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
6 Replies
Anonymous - Think of EARLIER as "current". So basically, the current value of something within the current context. Here is a good blog article about using EARLIER: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Greg_Deckler I have read that post and I am also calculation MTTR, MTBF but my data is not the same as yours. I have to filter differents dates overlapping and some other stuff. that's why I am taking small example to understand the iteration with earlier function.
can you explain me all iteration for any one ID of my dataset so that I can understand what's happening.
thanks for looking into my problem.
Good to see you are digging in to understand DAX with a simple example first. One comment. You used "measure" to describe what you put in a calculated column. While both Measures and Calculated Columns contain DAX expressions, they are not interchangeable. For example, the first variable in your expression (=Table1[Value1]) would return an error in a Measure. Calculated Columns have row context so it knows to get the value on that row (a measure would expect that column to be aggregated somehow).
I would encourage you to learn to write measures wherever possible, and only do Calculated Columns when you need something for the axis/category/legend of a visual, or for performance reasons.
In any case, the FILTER() function will return only rows that return TRUE(). In your case, there are three conditions. To pass all three, the iterated row (in FILTER()) has to
1 - be a different row than the current one (ID <> index)
2 - Value1 has to be less than the stored Value1
and
3 - Value2 has to be greater than the stored Value2
For the 1st row, there are no rows with Value1 <6, so you get blank
For the 2nd row, only ID 10 row meets all 3, so you get a result of 1.
For the 3rd row, only the 2nd row meets all 3, so you get a result of 1
For the 4th row, there are no rows with Value2 >21, so you get blank (not sure why you show 0 there)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thank you very much for your clarification.
still there is two more thing to understand.
in my Dax Expression i have created two variables
VAR _value1 = Table1[Value1] VAR _value2 = Table1[Value2]in the filter expression i have following formula
Table1[Value1]<=_value1 && Table1[Value2] >= _value2as per my understanding the above expression is equivalent to
Table1[Value1]<=EARLIER(Table1[Value1]) && Table1[Value2] >= EARLIER(Table1[Value2])correct?
2.
what is the difference when i write my expression something like this.
_value1 <= Table1[Value1] && _value2 >= Table1[Value2]it still evaulates the same way what you have explain in your comment or it is completly different.
my confusion is i have seen different post and in some post they wirte earlier function in the beginning and some time after so what will be the diffrence in my case. how my expression is caluclate in this sicenario.
Regads,
tar.
You might be interested in this article
https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/
EARLIER behaves differently depending on the scenario. Guidance seems to be to discontinue the use of EARLIER in favor of variables.
I slightly disagree with mahoneypat on the usage of calculated columns vs measures. Use calculated columns whenever you can (when the calculated data is not dependent on user interaction) and measures only if you must (when it is).
