Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count for each User ID

Hello Power BI Fam, 

 

I am having a hard time to come up with a DAX formula to fix the following issue: I have a User ID data item that has unique values for customers, and these customers participate in different programs that have their unique Program IDs. Each of those programs offer Activities for the participants.  Each participant can be a part of more than one program with more than one activity. 

 

Now, I need to calculate the number of User IDs that were not enrolled in Program #1. HOWEVER, when I just exclude that Program #1 in the filters, I get activities that do not have that program on them, but when I check the User ID, I see that this person was enrolled in Program 1, its just other programs come up after I filter. 

 

How can I just get the USER IDs that never received Program #1 services. 

 

This is how I was doing it in IBM Cognos using FOR function:

 

count( distinct
(case when [PROGRAM] = 'USA'
then 'Yes'
end )
for [UserID) = 0
then 'Yes'
end

 

Please help with the DAX formula. 

 

Thanks, 

Sona

Tips and Tricks
  • DataZoe's avatar
    DataZoe
    6 years ago

    Anonymous I think you were clear, I just didn't think it through. 🙂

     

    Please try this:

    Users = DISTINCTCOUNT(Data[UserID])
    No Program 1 = [Users]-CALCULATE([Users],Data[Program]=1)

5 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    Anonymous  I think you are saying that you have a  table with at least a "User ID", "Program", and "Activity" columns.

     

    If so, you could do a distinct count of the Users:

     

    Users = distinctcount(Table[User ID])

     

    And then you could get the non-program 1 version by this measure:

     

    Users In Program 1 = calculate([Users],Table[Program] <> "Program 1")

     

    If this is not it, can you please share the structure of your table? Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey DataZoe 

       

      Thank you so much for your prompt response. I think my question was not too clear. 

       

      Please see the screenshot below. 

       

      So I have this user ID 123456, and when I filter Program<>1, I sill get those Program 2 and 3 lines, which I dont want to. I want to get only records that do not have Program 1 at all. So i want to get a DAX formula that will exclude this record at all. 

       

      Thanks, 

      Sona

       

       

       
       
      • DataZoe's avatar
        DataZoe
        Microsoft Employee
        6 years ago

        Anonymous I think you were clear, I just didn't think it through. 🙂

         

        Please try this:

        Users = DISTINCTCOUNT(Data[UserID])
        No Program 1 = [Users]-CALCULATE([Users],Data[Program]=1)