Count by criteria

[StephenGW]

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

[v-angzheng-msft · Accepted solution]

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]

@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]

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]

@v-angzheng-msft 

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

StephenGW

[StephenGW]

@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

[parry2k]

@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]

Anybody got any ideas on this?

Thanks

[StephenGW]

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

[StephenGW]

@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=K9Ll... 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