Lookup in same column and count distinct

[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

[Mariusz]

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

[Anonymous]

@Mariusz Your measures are working perfectly. But i am not able to click the measure to do an interative drill down.

Any ideas pls?

[v-chuncz-msft · Accepted 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"
)

[Mariusz]

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

[Anonymous]

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]

@Mariusz You got any ideas for this requirement please..

[PattemManohar]

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

[Anonymous]

 @PattemManohar 

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.