Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lypabl
Regular Visitor

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.

 

2018-07-25_13h41_05.png

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

Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
erikajain02
Resolver I
Resolver I

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

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

Regards
Zubair

Please try my custom visuals

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

@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


Regards
Zubair

Please try my custom visuals

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?

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.