Forum Discussion
Add New Columns to Summary Table
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.
