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.

     

     

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