Forum Discussion
Accessible Reporting - Convert Matrix to Table, using Calculation Groups?
Hi Anonymous ,
You are absolutely right to look for a more accessible alternative - however, DAX does not support dynamic column names in a calculated table. This means we can't label columns like "July", "August", etc. based on date logic.
Instead, the best approach is to return rows with dynamic month labels and use a table visual, which is screen reader friendly.
Here’s a simplified calculated table that does this:
FlattenedClientReach =
VAR MaxMonthID = MAX('01b: Calendar'[Month Year ID])
RETURN
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATE(
'AT - CLIENT REACH',
VAR Client = 'AT - CLIENT REACH'[Client ID]
RETURN
FILTER(
ADDCOLUMNS(
'01b: Calendar',
"ReachValue",
CALCULATE(
'AT - CLIENT REACH'[Measures - Client Reach],
'AT - CLIENT REACH'[Client ID] = Client
)
),
'01b: Calendar'[Month Year ID] > MaxMonthID - 12 &&
'01b: Calendar'[Month Year ID] <= MaxMonthID
)
),
"MonthName", FORMAT('01b: Calendar'[Date], "MMM YYYY")
),
"Client", [Client ID],
"Month", [MonthName],
"ClientReach", [ReachValue]
)If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Hi Anonymous ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you shafiz_p for the prompt response.
you can generate a calculated table where each metric and month value is flattened into a single row per metric, with months as distinct columns.
Steps to Implement (Using DAX and Calculation Groups):
1.Create a Base Metrics Table:
If not already available, ensure you have a table that lists all your metrics like:
Metrics = DISTINCT('YourDataTable'[Client Reach Measures])
2.Create a Calculated Table (Flattened Table Format):
Use a DAX expression like this to pivot month data into columns:
FlattenedClientReach =
ADDCOLUMNS(
'Metrics',
"July", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 7),
"August", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 8),
"September", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 9),
"October", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 10),
"November", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 11),
"December", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 12),
"January", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 1),
"February", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 2),
"March", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 3),
"April", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 4),
"May", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 5),
"June", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 6)
)
Replace 'YourDataTable'[Value] with the appropriate measure field (e.g. leads, enquiries, etc.).
3.Use in a Table Visual:
Now, use the new FlattenedClientReach table in a Table visual. This visual will now:
- Treat each month as a distinct column.
- Use Client Reach Measures as the row headers.
- Allow screen readers to scan across months column-wise.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Hi v-venuppu ,
If I'm looking at Months and Financial Year, with this approach, would I need to create a column for each MM/YY value?