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 "blocked" test cases on the reporting date using the following measure:
Test Case Step-Blocked-Last =
      CALCULATE(
               COUNT('PH_TestCaseStep'[Test Case Step Status]);
                           FILTER('PH_TestCaseStep';'PH_TestCaseStep'[Date]=[Last date]);
                           FILTER('PH_TestCaseStep';'PH_TestCaseStep'[Test Case Step Status]="Blocked")
      )
 
The "Blocked" filter works, but the date filter does not work. Can you please tell me what's going on and how to overcome this?
 
Thanks,
Zoli
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    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" )
    )

12 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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" )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much!!!! Work fine.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Add ALL in date filter:

     FILTER( ALL ('PH_TestCaseStep');'PH_TestCaseStep'[Date]=[Last date]);

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Paul,

       

      I tried your version. It does not work, the measure still shows the aggerageted value of each day + the my slicer settings are ignored as well.

       

      I am getting lost 🙂

       

  • MCornish's avatar
    MCornish
    Responsive Resident
    4 years ago

    Try

    FILTER('PH_TestCaseStep'; 'PH_TestCaseStep'[Date]=[Last date] && 'PH_TestCaseStep'[Test Case Step Status]="Blocked")

     

    Not sure if it will work as I don;t have a good test case to try it on to hand

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your reply.

       

      The below measure aimed to calculate from the table all testcases happend on the [Last date]. Even this very simple filtering does not work, so I assume that a measure can't be used in the FILTER function. May be I am wrong...

       

       

      Test Case Step-All-Last = CALCULATE(COUNT('PH_TestCaseStep'[Test Case Step Status]);FILTER('PH_TestCaseStep';'PH_TestCaseStep'[Date]=[Last date]))
      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        You need to wrap the table for the FILTER in ALL, as I suggested in my previous post

  • Kumail's avatar
    Kumail
    Impactful Individual
    4 years ago

    Hello Anonymous 

     

    Is which visual you are using last date filter?

     

    If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.
     
    Regards
    Kumail Raza

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Kumail,

       

      I will try to send the pibx file, need to figure out how. Shall I send it private?

       

      My feeling is that meaure can't be used within the FILTER function. May be I am wrong....

      • Kumail's avatar
        Kumail
        Impactful Individual
        4 years ago

         

        You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

         
        Regards
        Kumail Raza

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.

         

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    Try this:

    Test Case Step-Blocked-Last =
CALCULATE(
    COUNT( 'PH_TestCaseStep'[Test Case Step Status] );
    FILTER(
        'PH_TestCaseStep';
        'PH_TestCaseStep'[Date]
            = CALCULATE( MAX( 'PH_Date'[Date] ); ALL( 'PH_Date'[Date] ) )
            && 'PH_TestCaseStep'[Test Case Step Status] = "Blocked"
    )
)

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

     

    Appreciate your Kudos!!

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Share some data, describe the business question and show the expected result.