Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I am trying to get a count of a count.
Sample data:
PERSON_ID | USE_ID |
1 | 333 |
1 | 444 |
1 | 555 |
2 | 666 |
2 | 777 |
3 | 888 |
4 | 999 |
5 | 1111 |
5 | 2222 |
5 | 3333 |
5 | 4444 |
At the moment I am counting the number of 'USE_ID' for each PERSON_ID', so
I am trying to make a measure that will show me the count of these counts, e.g:
1 PERSON_ID with 4 USE_ID,
1 PERSON_ID with 3 USE_ID,
1 PERSON_ID with 2 USE_ID,
2 PERSON_ID with 1 USE_ID.
The logic would then ideally be used so I can have:
Total of PERSON_ID with 1 USE_ID, 2-3 USE_ID, 4-5 USE_ID, 6-9 USE_ID, 10+ USE_ID.
Thanks!
Solved! Go to Solution.
@Anonymous
maybe you can try to create a new table
table2 = SUMMARIZE('Table','Table'[PERSON_ID],"count",count('Table'[USE_ID]))
right click the personID column and select count
Proud to be a Super User!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous ,
Try this:
1. Create a measure: Count of 'USE_ID' for each PERSON_ID'.
Count of 'USE_ID' for each PERSON_ID' = COUNT('Table'[USE_ID])
2. Create a table.
Num of count of USE_ID =
SUMMARIZE (
GENERATESERIES (
1,
MAXX ( VALUES ( 'Table'[PERSON_ID] ), [Count of 'USE_ID' for each PERSON_ID'] ),
1
),
[Value],
"No. of USE_ID", [Value] & " USE_ID"
)
3. Create a measure: Count of PERSON_ID with n USE_ID.
Count of PERSON_ID with n USE_ID =
VAR t =
ADDCOLUMNS (
'Table',
"count1", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[PERSON_ID] ) )
)
VAR t2 =
ADDCOLUMNS (
t,
"count2", CALCULATE (
DISTINCTCOUNT ( 'Table'[PERSON_ID] ),
FILTER ( t, [count1] = MAX ( 'Num of count of USE_ID'[Value] ) )
)
)
RETURN
IF (
HASONEVALUE ( 'Num of count of USE_ID'[No. of USE_ID] ),
MAXX ( t2, [count2] ),
DISTINCTCOUNT ( 'Table'[PERSON_ID] )
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
maybe you can try to create a new table
table2 = SUMMARIZE('Table','Table'[PERSON_ID],"count",count('Table'[USE_ID]))
right click the personID column and select count
Proud to be a Super User!
@Anonymous , You need, binning/ dynamic segmentation
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/
@amitchandak I'm finding this very difficult to understand after reading all the articles.
They all seem to related to the value within the column and not a count of that value appearing next to another column, like in my initial post.
Please can you elaborate on which article of the multiple articles applies to my example, and how?
Here is my visualisation with the count of USE_ID against the PERSON_ID. I want to group these but when I right-click, group cannot be selected and is greyed out.
Please can there be some further assistance here? The prior steer is insufficient, unfortunately after reading the articles I still don't quite understand.
Thanks.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.