Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Lookup in same column and count distinct

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 IDUpdate Status
1Update is installed
2Update is installed
3Update is installed
4Update is installed
5Update is installed
5Update is installed
7Update is installed
8Update is installed
1Update is required
2Update is required

 

Result:

Compliant (Distinct Count of Resource ID)8
Non-Compliant (Distinct Count of Resource ID)2
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    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"
)

8 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        Mariusz You got any ideas for this requirement please..

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

       

      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's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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"
)