Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous I think you were clear, I just didn't think it through. 🙂
Please try this:
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 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/
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
@Anonymous I think you were clear, I just didn't think it through. 🙂
Please try this:
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/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |