Explanation about Dax Expression filter

[tarun89engg]

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[Index]
VAR a =
    MAXX (  FILTER (
            ErrorLogs,
            (  ErrorLogs[End Date]  >= start_date		
                && ErrorLogs[End Date] <= end_date )
                && [Index] <> _index
        ),
        [End Date]
    )
VAR b =
    LOOKUPVALUE ( ErrorLogs[Index], ErrorLogs[End Date], a )
RETURN
  IF ( b < ErrorLogs[Index],a )

I have problem in interpreting the Filter Expression.

Here is picture of my data.

from my understanding it works like the following.

for first row with ID 2874029 it takes EndDate 3/29/2019 7:11:12 AM and check is there any value greater than this exist in virtual table in column StartDate & it also checks that is there any value less than this in virtual table in column EndDate, with a diffrent index other than that row.

based on my understanding the second row with a ID 2874029 has to retrun me data with value 3/29/2019 7:03:27AM in column Extended Date, but it is not so I think i am worng with my understanding.

some how i am unable to understand this logc how i have to interpret.

Please share some explantation in simple languare how i can interpert this logic.

the second thing i would like to know that is there any possibility to get a table with a applied filter for one ID and checks what it returns to me so that I can intrepret those value and see how it works. I mean anyway to debug and check for the first row what it returns after applying a filter expression.

Regads,

tar

[AntrikshSharma]

• 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

[tarun89engg]

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