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
Please see the attached file which uses Calculated column and MEASURE instead of CALCULATED TABLE
Hi,
Create this calculated column formula and name it as Stage
=IF(CALCULATE(DISTINCTCOUNT(Data[Update Status]),FILTER(Data,Data[Resource ID]=EARLIER(Data[Resource ID])))=1,"Compliant","Not compliant")
To your visual, drag the Stage column and write this measure
Measure = DISTINCTCOUNT(Data[Resource ID])
Hope this helps.
@Anonymous
Try this MEASURE
NonCompliant IDs = COUNTROWS ( FILTER ( VALUES ( Table1[Resource ID] ), CALCULATE ( DISTINCTCOUNT ( Table1[Update Status] ) ) > 1 ) )
@Anonymous
Try this calculated table
Please see attached file as well
Calculated Table = { ( "Non Compliant", COUNTROWS ( FILTER ( VALUES ( Table1[Resource ID] ), CALCULATE ( DISTINCTCOUNT ( Table1[Update Status] ) ) > 1 ) ) ), ( "Compliant", COUNTROWS ( VALUES ( Table1[Resource ID] ) ) ) }
Thanks for helping again. The result is accurate. We are nearing the completion.
I would like to create this result as a PIE. So that if I am clicking on the Non Compliant then i should see the Resource IDs. Also I have other dimensions like City, etc., So if I select the City then this Compliant and Non-Compliant values should change accordingly. Since this is a separate Calculated Table I dont know how to achieve this.
Or to be simple if you add one more column to your dataset like City and name few as USA and few as UK. Now if I am using this City as drop down filter the Calculated column should change. How to achieve this..?
Thanks in advance for your help..
@Anonymous
Please see the attached file which uses Calculated column and MEASURE instead of CALCULATED TABLE
Thanks for your timely help. It worked like a charm...
Hi,
But I need this like a dimension. I need a bar chart or pie chart as shown in my Post "Result". The measure is giving me the count.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |