Understanding the logic of variable + filter(allexcept(

[Anonymous]

Hi! I had this challange where i needed to check for first occurrance of a value depending on reportdate I got a code that work. And this is it:

FO = 
VAR curdate = Blad1[Reportdate]
VAR cureid = Blad1[Id]
VAR calc = 
CALCULATE(
    IF(
       cureid <> max(Blad1[Id]), 1, 0),
       FILTER(allexcept(Blad1, Blad1[Id]),
        curdate > Blad1[Reportdate]))
return 
calc

And the code works and all. But I just don't get it. I do think i understand that my variable for curdate, is evaluated against the whole column report date for each row, while the calculation is evulated. I also think i understand that allexcept in this context returns a table without the ID column, and with unique combination of values for the remaining columns which in this case is reportdate and estdeldate. In this case it is this sample data that i am working with:

Id                 reportdate    estdeldate

X-123	01/05/2021	01/09/2021
Y-125	01/05/2021	01/09/2021
Z-123	01/05/2021	01/09/2021
X-123	01/07/2021	01/09/2021
A-125	01/07/2021	01/09/2021
Y-125	01/07/2021	03/09/2021
Z-123	01/07/2021	03/09/2021
X-123	01/09/2021	01/09/2021
A-125	01/09/2021	01/09/2021
Y-125	01/09/2021	03/09/2021
Z-123	01/09/2021	06/09/2021
Z-124	01/09/2021	06/09/2021

But how can the IF function, evaluate the ID when the filter functions hides that value? Please can somebody explain this? Or send me somewhere where this is explained? 

Thanks a lot! 😄 

[mahoneypat · Accepted solution]

I think your column expression is working with a little bit of luck.  Your first two variables stored the values for Id and ReportDate on that row.  The ALLEXCEPT removes filters from all columns except the Id column and that table filter is further filtered to only the rows with an earlier report date.  In the true condition (result of 1), the reason they don't match is because the MAX is blank (no rows to get a max of).  For the later ReportDates, there are rows (with just the same Id), so the Id matches the max Id (the only Id present).  A different way to generate this column would be with this expression.

FO 2 =
VAR curdate = Blad1[Reportdate]
VAR cureid = Blad1[Id]
VAR rowsbeforesameid =
    CALCULATE (
        COUNTROWS ( Blad1 ),
        ALLEXCEPT ( Blad1, Blad1[Id] ),
        Blad1[ReportDate] < curdate
    )
RETURN
    IF ( rowsbeforesameid > 0, 0, 1 )

Pat

[Anonymous]

I think i understand. Not your explanation of my code though, but how your code works - 

Maybe you can comment if i got it. For example id X-123. 

It looks at the first date for the ID - 2021-05-01 - and compares it with the other dates for the same ID (I guess this is where the allexcept comes in). 2021-05-01, is not bigger than the other dates, therefore the filter returns a blank table and count i it rows, returning a blank value for that row. For the next date (for the same ID) it returns a table with one row, because this time it's evaluating the date 2021-07-01 which is bigger than 2021-05-01, hence returning that row, and then counts, returning the number 1. For the last date for the same ID it returns two rows (2021-07-01 and 2021-05-01), and the countrows returns 2. 

Eventually it checks the logic condition and returns a 1 for the rows with blank values, and a zero for rows where the value is bigger than 0, which means all the first occurances gets the value 1? 

English isn't my first langague but i hope i made some coherent sense here! 

Thanks!! 

[Anonymous]

And after writing this. I can see how my code works as well!! THANK U SO MUCH!!