The filter is not working as expected. I get a wrong result on a certain query

user_19541657 0
Viewed 109

new here. I have a basic excel I import in power BI. With 2 columns. Dates and results. The result is either good or bad. I built a function where it checks how many (work) days are good since the last bad day. While I have 2 function that works, I want to know why one doesn't work. This one doesn''t work:

>Measure =
CALCULATE (
    COUNT ( Table[Date] )
   ,Table[Result] = "Good"
   ,FILTER (
        Table
        ,Table[Date] > CALCULATE ( 
                           MAX ( Table[Date] )
                          ,Table[Result] = "bad" 
                       )
   )
) + 0

This apparently counts every good days and doesn't filter out since last bad one.

These work:

>Measure =
CALCULATE (
    COUNT ( Table[Date] )
    ,Table[Result] = "Good"
    ,FILTER (
        Table
        ,Table[Date]> CALCULATE(
                          MAX (Table[Date])
                          ,Table
                          ,Table[Result] = "bad" 
                      )
    )
) + 0



>Measure =
CALCULATE (
    COUNT ( Table[Date] )
    ,Table[Result] = "Good"
    ,FILTER(
        Table
        ,Table[Date]> CALCULATE(
                          MAX(Table[Date])
                          ,FILTER(
                               Table
                               ,Table[Result] = "bad" 
                           )
                       )
    )
) + 0

Can someone explain what the first one does "wrong"? Thanks in advance!

powerbi
2 Answers

Your DAX structure is problematic. You need to go step by step and follow the best DAX practices like using variables in your code:

Please test these DAX Codes: Version-1:

VAR MaxDate_With_Bad = CALCULATE(MAX(Table[Date]),Table[Result] = "bad")
VAR Filtered_Table = FILTER ( Table, Table[Date] > MaxDate_With_Bad)
VAR Result = 
CALCULATE ( COUNT ( Table[Date] ), Table[Result] = "Good", Filtered_Table)
RETURN Result

Version-2:

VAR MaxDate_With_Bad = CALCULATE(MAX(Table[Date]),Table[Result] = "bad")
VAR Filtered_Table = FILTER ( ALL(Table), Table[Date] > MaxDate_With_Bad)
VAR Result = 
COUNTROWS(
    CALCULATETABLE(
        Filtered_Table,
            Table[Result] = "Good"
    )
)
RETURN Result
user_194690880

It's a good question about how does DAX work. So in fact it comes to a difference between CALCULATE() expressions. The parent FILTER() goes through each row, row by row and compares if a value in a current row for certain column satisfies filtering conditions. Meanwhile CALCULATE() makes calculations fo for each row in a so called row context (please, read about it). A row contex it is when CALCULATE() use values in each row as filters ([Result] and [Date]).

So, this expression:

CALCULATE(              
    MAX(Table[Date])
    ,Table[Result] = "bad" 
)

is equal to

CALCULATE(              
    MAX(Table[Date])
    ,FILTER(
        ,ALL(Table[Result]) -- open the table with al "RESULT" type
        ,Table[Result] = "bad" -- filters opened table by "bad"
    )
    ,Table[Date] = Table[Date] -- (value in the current row) this filter 
                               -- is added by CALCULATE() automaticaly 
                               -- due to a row context mode
)

You always have a [Date] filter. If you have in a current row "bad" you get a value, if have "good" you have nothing. This is how you excludes "bad"() and have all "good". For example, you have in row [Date]=20 Jun 2022; [Result]="bad" CALCULATE() will return "20 Jun 2022", then you compare [Date](="20 Jun 2022")>"20 Jun 2022" this returns FALSE() and the row excludes. Then you have [Date]=21 Jun 2022; [Result]="good", CALCULTE() returns BLANK(). [Date](="21 Jun 2022")>BLANK() this returns TRUE() the row remains in the table.

Why does this CALCULATE works (see below)?

CALCULATE(              
    MAX(Table[Date])
    ,Table
    ,Table[Result] = "bad" 
)

It is equalt to:

CALCULATE(              
    MAX(Table[Date])
    ,ALL(Table)
    ,FILTER(
        ,ALL(Table[Result])
        ,Table[Result] = "bad"
    )
)

First works ALL(Table), according to DAX rules. It removes all filters all row context goes to hell and you deal with a Table as it is. Then you apply "bad".

If something still unclear, please, ask. If not, please, mark the answer.

user_190423790
Related