How to count amount of occurences

[Bokazoit]

I have multiple answers (HeartId) pr. user (ResponderId). We wants to know and display how many ResponderIds that have more than one HeartId:

Then we would like to group and display how many got only 1 heart disease, 2 heart diseases etc.

Can that be done?

[v-rzhou-msft · Accepted solution]

Hi @Bokazoit ,

I suggest you to select [Id] column and then unpivot other columns in Power Query Editor.

New table should look like as below.

Then create a count table.

Count = GENERATESERIES(1, 5, 1)

Measure:

Count ID = 
VAR _SUMMARIZE = SUMMARIZE('Table','Table'[Id],"Sum",CALCULATE(SUM('Table'[Value])))
RETURN
COUNTAX(FILTER(_SUMMARIZE,[Sum] = SUM('Count'[Count])),[Id])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bokazoit]

Thanks! that did the trick. Only needed to alter a few things 😉

[lbendlin]

Please provide sample data that covers your issue or question completely. As a Super User you can attach the data right here.

[Bokazoit]

I have added a simple view of sample data and the desired result:

ResponderId comes from my Fact and the type of Heart disease come from my DimHeartdisease. The third table is constructed for the purpose of showing the endresult.

The first table shows the data from the Fact and Dimension table. The easy part is to count the number of diseases pr. ResponderId (second table) . The hard part for me is to place each count in a table or Chart:

ResponderId 1001550 has 5 diseases and therefor it counts to 1 in the third table on first row with the number marked 5. ResponderId 1001557 has 3 diseases and therefor it counts to 1 in the third table on third row with the number marked 3.

The rest ReponderIds has 1 disease and that amounts to 8 placed in the fifth row with the number marked 1.