cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: Count for each User ID

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

View solution in original post

5 REPLIES 5
Highlighted
Responsive Resident
Responsive Resident

Re: Count for each User ID

@sonamyan  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!

Highlighted
Helper II
Helper II

Re: Count for each User ID

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, 

SonaCapture.PNG

 

 

 
 
Highlighted
Responsive Resident
Responsive Resident

Re: Count for each User ID

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

View solution in original post

Highlighted
Helper II
Helper II

Re: Count for each User ID

Thank for the solution, @DataZoe 

 

I still was not able to make it work because those data items come from different tables, my issue might be related to the realtishinip between them. But thanks for the solution, I am sure I will use it in the future. 

 

Thanks, 

 

Sona

Highlighted
Super User IV
Super User IV

Re: Count for each User ID

Hi,

Please share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors