Forum Discussion

StephenGW's avatar
StephenGW
Helper II
5 years ago
Solved

Count by criteria

Hello all,   I'm pretty good with spreadsheet formulae but I'm struggling with measures. I have a large data table with a few columns. Area, Audit Date, and Pass/Fail. What I need for a measure is ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, StephenGW 

    Try to create 2 measures like below:
    _AuditTime:

    _AuditTime = MAX('Data'[Audit Time])

    Probation:

    Probation =
VAR _table =
    SUMMARIZE (
        FILTER (
            'Data',
            RANKX ( ALLEXCEPT ( 'Data', Data[Area] ), [_AuditTime],, DESC ) <= 10
                && [Pass/Fail] = "Fail"
        ),
        [Area]
    )
RETURN
    IF ( MAX ( 'Data'[Area] ) IN _table, "Yes", "No" )

     Note:

           There are some data errors in your sample data. I have corrected the errors when using them.

    Result:

    Please refer to the attachment below for details

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
5 years ago

Hi, StephenGW 

Try to create 2 measures like below:
_AuditTime:

_AuditTime = MAX('Data'[Audit Time])

Probation:

Probation =
VAR _table =
    SUMMARIZE (
        FILTER (
            'Data',
            RANKX ( ALLEXCEPT ( 'Data', Data[Area] ), [_AuditTime],, DESC ) <= 10
                && [Pass/Fail] = "Fail"
        ),
        [Area]
    )
RETURN
    IF ( MAX ( 'Data'[Area] ) IN _table, "Yes", "No" )

 Note:

       There are some data errors in your sample data. I have corrected the errors when using them.

Result:

Please refer to the attachment below for details

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

  • StephenGW's avatar
    StephenGW
    Helper II
    5 years ago

    v-angzheng-msft 

     

    That works exactly as I asked. I need to make a small modification that I can't figure out. I have 4 slicers and I would like this measure to ignore 2 of them. I need it to ignore the slicer for Data[Audit Year] and Data[Audit Month] but not ignore the other two. Do you know how I can add that to this measure?

     

    Thanks,

    Stephen

  • StephenGW's avatar
    StephenGW
    Helper II
    5 years ago

    v-angzheng-msft 

     

    Is there anyway to make it ignore the two slicers I mentioned while keeping the other 2? When I change months it changes the outcome of this measure?

     

    Stephen

    • v-angzheng-msft's avatar
      v-angzheng-msft
      Community Support
      5 years ago

      Hi, StephenGW 

       

      Sorry for not checking the messages in time.
      You can add the filter you want to keep in the ALLEXCEPT function

      Something like this:

      Hope this is what you want.

       

      Best Regards,
      Community Support Team _ Zeon Zheng

      • StephenGW's avatar
        StephenGW
        Helper II
        5 years ago

        v-angzheng-msft 

         

        No problem. It works perfectly. Thank you very much!

         

        StephenGW