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
Anonymous
Not applicable

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

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
DataZoe
Employee
Employee

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

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

 

 

 
 

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

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

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
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.