Count distinct user with same status

[Lypabl]

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.

[erikajain02]

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

Change tablename and Field name accordingly.

[Lypabl]

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]

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 · Accepted solution]

@erikajain02@Lypabl

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 )

[NickPBI]

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

[Lypabl]

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]

@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

[erikajain02]

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]

Hi @erikajain02

Sorry I was out of office.

I am getting syntax error with your formula

Does the first part work with you?