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, ...
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 )
erikajain02's avatar
erikajain02
Icon for Resolver I rankResolver 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

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
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 )
  • erikajain02's avatar
    erikajain02
    Icon for Resolver I rankResolver I
    8 years ago

    Hi Zubair_Muhammad,

     

    Thanks for the new approach ,

     

    can you also please help me out to put an AND FILTER CONDITION for 

    Table1[sk_status] = 2

    in the Measure which I have tried ...!!!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Hi erikajain02

       

      Sorry I was out of office.

      I am getting syntax error with your formula

       

      Does the first part work with you?

       

  • Lypabl's avatar
    Lypabl
    Regular Visitor
    8 years ago

    Hi Zubair_Muhammad,

     

    I tried your formula and it is returning the correct value. However, I am not able to understand the use of 1 and 0 on the filter. Will you be able to explain it? Thanks.

     

    Regards,

    Lypabl

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Lypabl

       

      The formula check two conditions against each user

       

      1) There is a status 2 for that user ......  [CALCULATE ( DISTINCTCOUNT ( Table1[Users] ), Table1[sk_status] = 2 ) = 1]

      2) There is no other status for that user......[CALCULATE ( DISTINCTCOUNT ( Table1[Users] ), Table1[sk_status] <> 2) = 0]

       

      and then it

       

      Counts the users who meet these conditions

  • NickPBI's avatar
    NickPBI
    New Member
    3 years ago

    Muhammad,

    I have a question regarding this topic if would like to do this but filter in a period of time i.e. last 30 days. So it shows me each day the distinct count of the last 30, is it possible? doing this with time as well?

    Thanks