Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 )
you can use somehing like : CountofUsers = CALCULATE(DISTINCTCOUNT(Data[Users]) , FILTER(Data,[sk_status]=2))
Change tablename and Field name accordingly.
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.
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
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 )
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
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
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
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 ...!!!
Hi @erikajain02
Sorry I was out of office.
I am getting syntax error with your formula
Does the first part work with you?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |