Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I am trying to calculate the distinct count of Resource ID if the it has only one status. If you look at the below table Resource ID 1 and 2 has the status "Update is installed" and "Update is required" so I am taking this disticnt count for "Non-Compliant". Other Resource IDs have only one status so they are compliant. I have no clue on how to achieve this. Please help
Resource ID | Update Status |
1 | Update is installed |
2 | Update is installed |
3 | Update is installed |
4 | Update is installed |
5 | Update is installed |
5 | Update is installed |
7 | Update is installed |
8 | Update is installed |
1 | Update is required |
2 | Update is required |
Result:
Compliant (Distinct Count of Resource ID) | 8 |
Non-Compliant (Distinct Count of Resource ID) | 2 |
Solved! Go to Solution.
@Anonymous ,
You may add the following calculated column.
Column = IF ( ISEMPTY ( FILTER ( Table1, Table1[Resource ID] = EARLIER ( Table1[Resource ID] ) && Table1[Update Status] <> EARLIER ( Table1[Update Status] ) ) ), "Compliant", "Non-Compliant" )
Hi @Anonymous
Please see the Measures below.
Compliant = first Measure VAR c = CALCULATETABLE( VALUES(Table[Resource ID]), Table[Update Status] = "Update is installed" ) VAR n = CALCULATETABLE( VALUES(Table[Resource ID]), Table[Update Status] = "Update is required" ) RETURN COUNTROWS( EXCEPT(c, n) ) second Measure: Non-Compliant = CALCULATE( DISTINCTCOUNT(Table[Resource ID]), Table[Update Status] = "Update is required" )
Hope this helps.
Mariusz
@Mariusz Your measures are working perfectly. But i am not able to click the measure to do an interative drill down.
Any ideas pls?
@Anonymous ,
You may add the following calculated column.
Column = IF ( ISEMPTY ( FILTER ( Table1, Table1[Resource ID] = EARLIER ( Table1[Resource ID] ) && Table1[Update Status] <> EARLIER ( Table1[Update Status] ) ) ), "Compliant", "Non-Compliant" )
Hi @Anonymous
Were ID 1, 2 shoud be allocated in Compliant (Distinct Count of Resource ID)
or Non-Compliant (Distinct Count of Resource ID) ?
Mariusz
You can see two status for ID 1 and 2.
If the Resource ID is only a member of "Update is installed" then it should be counted for Compliant.
If the Resource ID is a member of "Update is installed" and "Update is required" then thouse Resource IDs should be counted only for Non-Compliant
@Anonymous Please try this as a New Table
Test312Out = VAR _Complaint = ROW("Type","Complaint","Count",CALCULATE(DISTINCTCOUNT(Test312DistinctCount[Resource ID]),Test312DistinctCount[Update Status]="Update is Installed")) VAR _NonComplaint = ROW("Type","Non-Complaint","Count",CALCULATE(DISTINCTCOUNT(Test312DistinctCount[Resource ID]),Test312DistinctCount[Update Status]="Update is Required")) RETURN UNION(_Complaint,_NonComplaint)
Note - You have resource id 5 two times so the complaint count will be 7 but not 8 as you mentioned in the expected output.
Proud to be a PBI Community Champion
I am able to replicate the result provided but the Compliant is not just distinct count. For Resource ID 1 and 2 they are also a member of Update is is required so they has to be Non Compliant.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |