Add New Columns to Summary Table

[Ormy28]

Hi

I'm quite new to DAX, so there might be a straightforward solution to this.

I have a table of data as below, with dates and a colour.

ID	Week Start	Colour
1	07/04/2025	Red
2	07/04/2025	Red
3	07/04/2025	Red
4	07/04/2025	Blue
5	07/04/2025	Green
6	14/04/2025	Red
7	14/04/2025	Red
8	14/04/2025	Blue
9	14/04/2025	Blue

I have created a summary table SUMMARIZE that would return the below:

Week Start	RED	BLUE	GREEN
07/04/2025	3	1	1
14/04/2025	2	2	0

This is how I did this:

SUMMARIZE('Table1', 'Table1'[Start of Week],

"RED", CALCULATE(COUNT('Table1'[Colour]), FILTER('Table1', [Colour] = "Red")),

"BLUE", CALCULATE(COUNT('Table1'[Colour]), FILTER('Table1', [Colour] = "Blue")),

"GREEN", CALCULATE(COUNT('Table1'[Colour]), FILTER('Table1', [Colour] = "Green"))

)

However, a new colour could appear in the future. Therefore, if Yellow was to appear in week starting 21/04/2025, I would need to amend the above DAX to pick up this new colour.

Is there any way to create a summary table that would list all colours for a particular date, along with the associated count, without having to amend the DAX when they appear? I might be heading down the wrong avenue using SUMMARIZE and there is a more efficient way of doing this. Any help would be greatly apreciated.

Thanks.

[PijushRoy · Accepted solution]

Hi @Ormy28 

Plase use below DAX 

DynamicSummary =
SUMMARIZE(
    'Table', 
    'Table'[Week Start], 
    'Table'[Colour],
    "Count", COUNT('Table'[ID])
)

[Ormy28]

Excellent, thanks for the help. A straightforward and logical solution.