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 for each unique Area I need to find the most recent Audit Date where the Pass/Fail is a fail. So find the most recent fail for each unique area. Then count how many Passes since the most recent fail. What happens is when an area fails they get put on a probationary status. They must then get 10 passing audits to be removed from probationary status. I want to put this in a table and use conditional formatting to color the areas red that are in probation. Is this even possible with a measure?

 

Thanks,

Stephen

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.

     

     

9 Replies

  • 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

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    StephenGW It will be easier if you share sample data with the expected output, and if you can share a pbix with sample data even better. Remove any sensitive information before sharing.

    • StephenGW's avatar
      StephenGW
      Helper II
      5 years ago

      parry2k 

       

      Sure I have created a a very simplified sample with sensitive data changed. But when I try to share the pbix it says that file type is not supported. Here is a link to the sample data. https://valmont-my.sharepoint.com/:x:/p/slg_na/EcZbe3m-ChNMhafKmIUOHrABVlZhjOnVCFrc_Shp-eATzA?e=K9LlBE in this sample areas Inside and Underground would be on probation because they have a recent fail with less than 10 passes since the most recent fail. In my BI I would like to have a table that will count how many passes since the most recent fail for each area and if it is less than 10 passes since the fail it would output yes to note that the area is on probation.

       

      If you can explain how to upload a pbix I have a sample for this data too but it just has a table with areas in it and a simple chart as well. Drag and drop would not work.

       

      StephenGW

    • StephenGW's avatar
      StephenGW
      Helper II
      5 years ago

      Also if it helps this data comes in from a Power App that I created so that might affect some of the formatting if that affects the measure.

       

      StephenGW