Forum Discussion
Understanding the DAX Measure
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
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] >= _value2 as 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).
You are correct on both.
1. in this case, the use of variables and EARLIER should get same results. With variables, you might never need EARLIER or EARLIEST again. Variables are easier to understand and more robust IMO.
2. Changing the order shouldn't matter. Each iterated row inside the FILTER is evaluated for the given expression.
Regards,
Pat
