Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Explanation about Dax Expression filter

Hi all  again i have problem in understanding the following dax expression.   Extended date = VAR start_date = ErrorLogs[Start Date] VAR end_date = ErrorLogs[End Date] VAR _index = ErrorLogs[Ind...
Need Help
Tips and Tricks
AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
6 years ago
  • First the VAR/Variables store the values from the currently iterated row, in simple terms starting with second row and then moves on to other row and stores the values of the current row. In terms of excel if you are at D1 then store values of A1, B1, C1, next you drag formula to D2 then store values of A2, B2, C2.

 

  • FILTER iterates the whole table inside it  for each row of the table that is outside it, if we are at 2nd row then whole table is iterates inside FILTER for that 2nd row, same for 3rd row, 4th row ……..Nth row, now FILTER checks if the EndDate of the table inside FILTER is greater than the start date stored in the variable which takes it from the table that is available outside of the FITLER, then does similar logic from End date & for index keeps the rows where indexes do not match( in practice only 2nd row remains once FITER is done iterating), actually I use terms “table outside” and “table inside” while the real term is row context, there are 2 row context here 1 that is created by the calculated column on the table and the one that is created by FILTER since row context of FILTER hides the row context of Calculated column we use either Variables or EARLIER function

 

  • MAXX gets only 1 row so nothing needs to be done, by default that is the max value,

 

  • LOOKUPVALUE return the Index where EndDate in the table matches value return by a

 

  • At the end if index of b is less than Index of the current row then return a which is true because 2874024 is less than 2874029
Anonymous's avatar
Anonymous
Not applicable
6 years ago

AntrikshSharma 

Thanks for explaining but still not clear to me.

 

Just explain me how should i have to intrepet the expression in filter.

for an ID 2874029 why i am getting extended date value and why not for ID 2874024.

 

for.ex if i had below expression in filter 

 

 FILTER (
            ErrorLogs,
            (  ErrorLogs[TimeDiff]>200  )
                && [Index] <> _index
        )

than for each ID it compare the TimeDiff value is greater than 200 . so for this case filter return me for a First ID value 3 as an output because 1241 , 205 and 286 is the only value greater than 200 for first ID.

 

and it compare like this for all other rows. 

 

How it works for this filter expression.

 

thanks for taking your time.