Forum Discussion
Count if and lookup inside same column
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 |
Anonymous
Please see the attached file which uses Calculated column and MEASURE instead of CALCULATED TABLE
9 Replies
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 ) )- I will get back to you in an hour or so.
I am out of office now
