topic Re: How to calculate the Overall Status from Duplicate rows in PowerBI in DAX Commands and Tips

How to calculate the Overall Status from Duplicate rows in PowerBI

Anonymous — Tue, 11 Feb 2020 19:05:56 GMT

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

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

az38 — Tue, 11 Feb 2020 19:14:25 GMT

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

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

Anonymous — Tue, 11 Feb 2020 19:55:47 GMT

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.

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

az38 — Tue, 11 Feb 2020 20:07:38 GMT

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

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

Anonymous — Mon, 17 Feb 2020 10:44:53 GMT

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

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

Anonymous — Mon, 17 Feb 2020 11:03:41 GMT

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.

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

Anonymous — Tue, 18 Feb 2020 18:21:33 GMT

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

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

az38 — Tue, 18 Feb 2020 18:59:28 GMT

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

Re: How to calculate the Overall Status from Duplicate rows in PowerBI

Anonymous — Tue, 18 Feb 2020 19:14:34 GMT

az38 thanks this Helps ! 🙂