Understanding the DAX Measure

[Anonymous]

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.

Excel File 

Powerbi.pbix 

Any explanation is greatly appreciated.  

[mahoneypat · Accepted solution]

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

[Anonymous]

@mahoneypat 

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.

[mahoneypat]

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

[lbendlin]

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

[Greg_Deckler]

@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

[Anonymous]

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