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.
I have a data set that I need to perform a count of systems selected by a user (either 1, 2 or 3). I then will take that result and do a count of the number of users that picked 1 system, 2 system or 3 systems. Data is structured as follows:
User | System | System Selected |
User1 | Secondary Business System | No |
User1 | Tertiary Business System | No |
User1 | Primary Business System | No |
User1 | Unlisted Business Systems | No |
User2 | Primary Business System | Yes |
User2 | Secondary Business System | No |
User2 | Tertiary Business System | No |
User2 | Unlisted Business Systems | No |
User3 | Secondary Business System | Yes |
User3 | Primary Business System | Yes |
User3 | Unlisted Business Systems | No |
User3 | Tertiary Business System | No |
User4 | Secondary Business System | Yes |
User4 | Unlisted Business Systems | No |
User4 | Tertiary Business System | No |
User4 | Primary Business System | Yes |
user5 | Unlisted Business Systems | No |
user5 | Secondary Business System | Yes |
user5 | Primary Business System | Yes |
user5 | Tertiary Business System | Yes |
user6 | Tertiary Business System | No |
user6 | Primary Business System | No |
user6 | Unlisted Business Systems | No |
user6 | Secondary Business System | No |
And the resulting set by user should be
User | Total Systems Selected |
User1 | 0 |
User2 | 1 |
User3 | 2 |
User4 | 2 |
user5 | 3 |
user6 | 0 |
and then the final system count would look like
Total Systems Selected | # Users |
0 | 2 |
1 | 1 |
2 | 2 |
3 | 1 |
I know it's a count function, but I'm just not quite sure how to approach it in power bi.
Solved! Go to Solution.
Try this measure, sustituting Table with your actual table name for your first table (with User column).
UserSelectedCount = Calculate(DistinctCount(Table[System]), Table[System Selected]="Yes")
For the second table, you'll need another table that has the 0,1,2,3. You can generate a DAX table with
Counts = GENERATESERIES(0, 3, 1)
No relationship to any other table needed.
You can then make a table with the Counts[Values] column and this measure.
SystemCount = var selectedcount = Selectedvalue(Counts[Value])
return Countrows(Filter(Values(Table[User]), [UserSelectedCount] = selectedcount))
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
I test the method that @mahoneypat suggested. My sample works fine. You just used the wrong formula.
The second table:
Counts = GENERATESERIES(0, 3, 1)
The measure:
SystemCount =
var selectedcount = Selectedvalue(Counts[Value])
return
Countrows(Filter(Values('Table'[User]), [UserSelectedCount] = selectedcount))
Try this measure, sustituting Table with your actual table name for your first table (with User column).
UserSelectedCount = Calculate(DistinctCount(Table[System]), Table[System Selected]="Yes")
For the second table, you'll need another table that has the 0,1,2,3. You can generate a DAX table with
Counts = GENERATESERIES(0, 3, 1)
No relationship to any other table needed.
You can then make a table with the Counts[Values] column and this measure.
SystemCount = var selectedcount = Selectedvalue(Counts[Value])
return Countrows(Filter(Values(Table[User]), [UserSelectedCount] = selectedcount))
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I was able to create the measure and the count table but the second table is giving me an error. I used the syntax:
SystemCount = var selectedcount = Selectedvalue(Counts[Value])
to create the DAX table, but it is saying the syntax for ')' is incorrect. I may be misunderstanding that last step. I feel like it's close I just need a little clarification on the last part.
Hi @Anonymous ,
I test the method that @mahoneypat suggested. My sample works fine. You just used the wrong formula.
The second table:
Counts = GENERATESERIES(0, 3, 1)
The measure:
SystemCount =
var selectedcount = Selectedvalue(Counts[Value])
return
Countrows(Filter(Values('Table'[User]), [UserSelectedCount] = selectedcount))
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |