Count if and lookup inside same column

[Anonymous]

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

[Ashish_Mathur]

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.

[Zubair_Muhammad]

@Anonymous 

Try this MEASURE

NonCompliant IDs =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Resource ID] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Update Status] ) ) > 1
    )
)

[Anonymous]

@Zubair_Muhammad 

Any ideas please

[Zubair_Muhammad]

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

[Anonymous]

@Zubair_Muhammad 

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..

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Please see the attached file which uses Calculated column and MEASURE instead of CALCULATED TABLE

[Anonymous]

Thanks for your timely help. It worked like a charm...

[Zubair_Muhammad]

I will get back to you in an hour or so.

I am out of office now

[Anonymous]

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.