How to count when a criteria isn't met?

[Anonymous]

Hi, I have data that looks like this:

I want to get counts of Compliant and non-compliant assets. An asset is compliant if it has no non-compliant vulnerabilities.

Here is the non-compliant asset count measure:

non_compliant_asset_count = 
CALCULATE(
    DISTINCTCOUNTNOBLANK(all_details[asset_id]),
    'all_details'[compliance] = "Non-compliant"
)

What I imagine compliant_asset_count should look like:

compliant_asset_count = 
CALCULATE(
    DISTINCTCOUNTNOBLANK(all_details[asset_id]),
    'all_details'[compliance] != "Non-compliant"
)

 Obviously that doesn't work since it counts the assets with a compliant vulnerability, even if they have a non-compliant vulnerability too.

If I was doing this in Python I'd do a foreach loop, but AFAIK you can't do those in Power Bi.

Any help would be great!

[PaulDBrown]

@Anonymous 

For Compliant Assets, how about:

Count of Compliant Assets (Unique)=
VAR AllAssets = VALUES(all_details[asset_id])
VAR NonComp = CALCULATETABLE(VALUES(all_details[asset_id]),
FILTER(all_details,
all_details[compliance] = "Non-compliant"))
RETURN
COUNTROWS (EXCEPT(AllAssets, NonComp))

Explanation:

VAR ALLAssets: creates a table listing all unique asset_id

VAR NonComp: creates a table listing all unique asset_id which (at some point) are non-compliant

Function EXCEPT: creates a table excluding all unique ids which (at some point) are non-compliant from the total list of unique asset_ids. This table therefore only includes asset_ids which have not been non-compliant at some stage.

EDIT: actually come to think about it, you could just subtract the distinctcoun of non-compliant ids from the distinctcount of all ids...

You can use the EXCEPT method for example if you need to perform some other calculation where you need the compliant only values. 

To list the asset_id which are only "compliant" you can use:

Compliant Assets =
VAR AllAssets = VALUES(all_details[asset_id])
VAR NonComp = CALCULATETABLE(VALUES(all_details[asset_id]),
FILTER(all_details,
all_details[compliance] = "Non-compliant"))
RETURN
CONCATENATEX((EXCEPT(AllAssets, NonComp)), all_details[asset_id], ", "))

You can also use the above EXCEPT expression to create a new table, create a relationship with the all-details table and use this new table as a slicer.

[v-kelly-msft]

Hi @Anonymous ,

You can use the following measures:

//Count the number of Compliant

number of Compliant = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[asset_id]),'Table'[compliance]="Compliant")

//Count the number of non-compliant

number of Non-Compliant = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[asset_id]),FILTER('Table','Table'[compliance]="Non- Compliant"))

If you wanna count the number whose compliant value isnt "non-compliant" ,you can use a measure as below:

Number of not meet = IF(SELECTEDVALUE('Table'[compliance])<>"Non- Compliant",DISTINCTCOUNTNOBLANK('Table'[asset_id]),BLANK())

Finally you will see:

For the related .pbix file ,pls click here.

Best Regards,

Kelly

[danextian · Accepted solution]

Hi @Anonymous ,

Try this as a measure:

Compliant Count =
//count the number of rows per asset_id that is non-compliant
VAR _NON_COMPLIANT =
    CALCULATE (
        COUNTA ( 'Table'[asset_id] ),
        ALLEXCEPT ( 'Table', 'Table'[asset_id] ),
        'Table'[compliance] = "Non-Compliant"
    )
RETURN
    //if count is 0 then compliant
    //show the unique count of compliant assets
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[asset_id] ),
        FILTER ( 'Table', _NON_COMPLIANT = 0 )
    )

Or you can create a calculated column which you can use in a slicer or filter your data by:

Compliant/Non-Compliant =
//count the number of rows per asset_id that is non-compliant
VAR _NON_COMPLIANT =
    CALCULATE (
        COUNTA ( 'Table'[asset_id] ),
        ALLEXCEPT ( 'Table', 'Table'[asset_id] ),
        'Table'[compliance] = "Non-Compliant"
    )
RETURN
    IF ( _NON_COMPLIANT > 0, "Non-Compliant", "Compliant" )