Forum Discussion

Lypabl's avatar
Lypabl
Regular Visitor
8 years ago
Solved

Count distinct user with same status

Hi Experts,

 

I have a table which contains the below data. I want to count the # of distinct users with only "2" as their status. For User with ID # 10 and 13 highlighted on the screenshot below, they should not be included on the count. Please advise on how to achieve this using DAX.

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    erikajain02Lypabl

     

    How about this one

     

    Measure =
VAR users =
    FILTER (
        VALUES ( Table1[Users] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Users] ), Table1[sk_status] = 2 )
            = 1
            && CALCULATE ( DISTINCTCOUNT ( Table1[Users] ), Table1[sk_status] <> 2 )
                = 0
    )
RETURN
    COUNTROWS ( users )

9 Replies

  • erikajain02's avatar
    erikajain02
    Resolver I
    8 years ago

    you can use somehing like : CountofUsers = CALCULATE(DISTINCTCOUNT(Data[Users]) , FILTER(Data,[sk_status]=2))

    Change tablename and Field name accordingly.

    • Lypabl's avatar
      Lypabl
      Regular Visitor
      8 years ago

      Hi Erickajain02,

       

      Thanks for the reply. If I do distinct User with ID # 10 and 13 (highlighted) will still be on the list. I need this two to be removed from the list.

      • erikajain02's avatar
        erikajain02
        Resolver I
        8 years ago

        Sorry , i misunderstood your requiremnt before.

         

        I think this should work :

        if( CALCULATE(COUNTROWS(VALUES(Status)) = 1,ALLEXCEPT(Data,Data[Users])),DISTINCTCOUNT(Data[Users))

        You can add Filter Condition for status as  : 2    as well here