Need a new column to count each combination

[RichOB]

Hi, I need to make a summarized table and add a column to count the number of incidents for each combination.

The first table below is my original, the 2nd one is what I need it to look like. What DAX will add that column, please?

Incident Type	Location	Date	Financial Quarter
Missing person	Glasgow	02/08/2024	Q2
Missing person	Glasgow	02/08/2024	Q2
Medical concern	Glasgow	01/11/2024	Q3
Missing person	Glasgow	01/11/2024	Q3
Missing person	Glasgow	01/11/2024	Q3
Medical concern	Edinburgh	02/08/2024	Q2
Medical concern	Edinburgh	02/08/2024	Q2
Missing Person	Edinburgh	02/08/2024	Q2
Medical concern	Edinburgh	01/11/2024	Q3
Medical concern	Edinburgh	01/11/2024	Q3
Aggression	Manchester	02/08/2024	Q2
Aggression	Manchester	02/08/2024	Q2
Aggression	Manchester	01/11/2024	Q3
Aggression	Manchester	01/11/2024	Q3
Medical concern	Manchester	01/11/2024	Q3

Incident Type Location Financial Quarter Count

Missing Person	Glasgow	Q2	2
Missing Person	Glasgow	Q3	3
Medical Concern	Glasgow	Q3	1
Medical Concern	Edinburgh	Q2	2
Medical Concern	Edinburgh	Q3	2
Aggression	Manchester	Q2	2
Aggression	Manchester	Q3	2
Medical Concern	Manchester	Q3	1

Thanks

[Anonymous · Accepted solution]

Hi,

Thanks for the solution pborah  offered, and i want to offer some more information for user to refer to.

hello @RichOB , you can try the following two solutions.

1.If you need to create a summerized table, you can create a new calculated table.

Table2 =
SUMMARIZE (
    'Table',
    [Incident Type],
    'Table'[Location],
    'Table'[Financial Quarter],
    "Counts", COUNTROWS ( 'Table' )
)

Output

2.If you just want the counts you can refer the following sloutions:

Soluton1:

Create a calculated column.

Counts =
COUNTROWS (
    FILTER (
        'Table',
        [Incident Type] = EARLIER ( 'Table'[Incident Type] )
            && [Location] = EARLIER ( 'Table'[Location] )
            && [Financial Quarter] = EARLIER ( 'Table'[Financial Quarter] )
    )
)

Output

Solution2: Create a measure. and put the measure to the visual.

Measure = COUNTROWS('Table')

 Output

Best Regards!

Yolo Zhu

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

[pborah]

@RichOB you need to create sort of unique keys for each combination of incident type, location, and financial quarter. It could be as simple as creating a column concatenating those three values, usually something like-

New Field = IncidentType&Location&Quarter

Then you can drop that calculated column in your "columns" card if you're using a table visual and set that column to count.

[RichOB]

Hi @pborah thanks for replying, I've made the unique keys using your suggestion, but would you know how to get the numbers in the table column instead of setting the visual to "count"? I need to use table column to make a lot of mesures. Sorry should have mentioned that. Thanks again!

[pborah]

hey @RichOB , no problem. Can you perhaps respond with a desired output with all your measures? Please check the screenshot below for how to get the counts. After setting the UniqueKey field to count, I renamed that to display as QuarterCount. Feel free to shoot any further questions if needed.