Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering table with date

Hi All,   I have a measure that works fine to define the lats reported date in my table:   Last date = CALCULATE(MAX( 'PH_Date'[Date]);ALL('PH_Date'[Date])) Now, I would like count all "blocke...
  • AlexisOlson's avatar
    4 years ago

    Calling a measure inside of an iterator like FILTER means a context transition happens that transforms all of the row context into filter context, so that even though [Last date] removes the Date filter context, all of the other column values in that row are still being applied as filter context, in which case you aren't getting the max over the whole table as you expect.

     

    Often, it's easier to sidestep the problem using variables. This also has the advantage of only calculating that measure once instead of for each row of the table you're filtering.

    Test Case Step-Blocked-Last =
    VAR LastDate = [Last date]
    RETURN
        CALCULATE (
            COUNT ( 'PH_TestCaseStep'[Test Case Step Status] );
            'PH_TestCaseStep'[Date] = LastDate;
            'PH_TestCaseStep'[Test Case Step Status] = "Blocked"
        )

     

    Depending on how you want the measure to behave, you may want to keep existing filters on the table rather than replace them. In that case, use

    Test Case Step-Blocked-Last =
    VAR LastDate = [Last date]
    RETURN
        CALCULATE (
            COUNT ( 'PH_TestCaseStep'[Test Case Step Status] );
            KEEPFILTERS ( 'PH_TestCaseStep'[Date] = LastDate );
            KEEPFILTERS ( 'PH_TestCaseStep'[Test Case Step Status] = "Blocked" )
        )