How to calculate the Overall Status from Duplicate rows in PowerBI

[Anonymous]

I want to calculate the overall status of the filed based on status avaialble in different column in the data table 

Sample Data 

Compliance Control Description	State	Compliance Control	subscription ID
VM Backup	Pass	A	1
VM Backup	Fail	A	2
VM Backup	Skipped	A	3
Pacthing	Pass	B	1
Pacthing	Fail	B	2

For eg . 

 If any one of the value for VM backups Fail or skipped then the overall status should be "Fail"

I want to calculate the overall status of "VM Backup" as "Fail" because one of the value in State cloum is falied for VM backup.Please assist

[Anonymous]

If you want the overall status in a new calculated column:
 
OverallStatus =
IF(COUNTROWS(
   FILTER(ALL('Table'), [Compliance Control Description] = EARLIER([Compliance Control Description])
   && [State] IN { "Fail", "Skipped"} ) > 0, "Failed", "Not failed")
 
You can then use SUMMARIZE to group by Compliance Control Description / OverallStatus and count the ones failed.

[Anonymous]

@Anonymous  Getting the Error "Parameter is not the correct Type" @az38 

Adding the Data from the actual table for referance . Here I need to idetify the overall status as Failled, Passed , Skipped for Distinct Content.RegulatoryComplianceDescription.

If any RegulatoryComplianceDescription is Failing or any one row . it should show the Overall status as Failed . Similarly if all Passed then OverAll Status as Passed .

Then need to count all Passed and Failed .

Please help . Thanks

Content.RegulatoryComplianceControlPassedAssessments	Content.RegulatoryComplianceControlState	Content.RegulatoryComplianceControlDescription	Content.RegulatoryComplianceControlName	Content.RegulatoryComplianceStandardName	Content.RegulatoryComplianceControlFailedAssessments	Content.RegulatoryComplianceControlSkippedAssessments
0	Skipped	Access to all audit trails	10.2.3	PCI-DSS-3.2.1	0	66
66	Passed	Access to all audit trails	10.2.3	PCI-DSS-3.2.1	0	0
59	Failed	Access to all audit trails	10.2.3	PCI-DSS-3.2.1	7	0
3	Passed	Access to confidential information from outside the boundaries of the system and disclosure of confidential information is restricted to authorized parties in accordance with confidentiality commitments and requirements.	C1.3	SOC-TSP	0	47
47	Failed	Access to confidential information from outside the boundaries of the system and disclosure of confidential information is restricted to authorized parties in accordance with confidentiality commitments and requirements.	C1.3	SOC-TSP	3	0
2	Failed	Management of privileged access rights	A9.2.3	ISO-27001	2	43

[az38 · Accepted solution]

@Anonymous 

you can create a calculated column, not measure

Overall Status Column = 
var _countFails = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Content.RegulatoryComplianceControlDescription]),OR('Table'[Content.RegulatoryComplianceControlState]="Failed",'Table'[Content.RegulatoryComplianceControlState]="Skipped"))
return
IF(_countFails>0,"Failed","OK")

then as @Anonymous mentioned create a calculated table

Table 2 = SUMMARIZE('Table', 'Table'[Overall Status Column], "Count Status", DISTINCTCOUNT('Table'[Content.RegulatoryComplianceControlDescription]))

[Anonymous]

@az38  thanks this Helps ! 🙂

[az38]

Hi @Anonymous 

if i understand you correct try a measure

Overall Status = 
var _countFails = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Compliance Control]),OR('Table'[State]="Fail",'Table'[State]="Skipped"))
return
IF(_countFails>0,"Fail","OK")

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Thanks @az38  this really helps to get the overall status.

Also can we calculate the count of how many are passed and how many are skipped / failed  based on the overall status and Compliance Control.

Compliance Control Description	State	Compliance Control	subscription ID
VM Backup	Pass	A	1
VM Backup	Fail	A	2
VM Backup	Skipped	A	3
Pacthing	Pass	B	1
Pacthing	Pass	B	2

Like for Eg in the sample Data

VM Backup  = Failed (because for one subscription it failed)

Patching  = Passed  ( for all it passed)

So For Complaince Control A it shows 1 passed and 1 failed

Out put like

Compliance Control	Passed Description
A	0
B	1

I have a huge data set and I want to bascically plot in a Bar Graph on

How many unique control description passed for a given complinace control and out of total unique control description for a given complince

Note the table as duplicate enties for a given Compliance Control Description.

[az38]

@Anonymous 

i do not understand how do you get Passed Description=1 for B ? is it count of unique descriptions?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 

Yes it is the unique for the description. So If all State is Passed it should mark the description as Passed and I want to calcualte the total Passed/Failed description.

Data set is big so I have shared the smaller eg. Please assist . Thanks