Count of Count

[Anonymous]

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

• PERSON_ID 1 has a count of 3 USE_ID,
• PERSON_ID 2 has a count of 2 USE_ID
• PERSON_ID 3 has a count of 1 USE_ID
• PERSON_ID 4 has a count of 1 USE_ID
• PERSON_ID 5 has a count of 4 USE_ID

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!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Icey]

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.

[ryan_mayu · Accepted 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

[amitchandak]

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

[Anonymous]

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