Forum Discussion
Dynamic Matrix Column Switch (Week → Period → Date) using Slicer Not Working
Hi All,
I have 3 matrix views working individually:
Last 5 Weeks (Slicer) → Column: Actual Goods Issue Fiscal Week
Last 2 Fiscal Periods (Sliicer) → Column: Actual Goods Issue Fiscal Period
Last 30 Days (Slicer) → Column: Actual Goods Issue Date
All 3 results are correct separately.
Now I want to merge into one matrix and switch the column axis using slicer (Week / Period / Date).
Issue:
Tried Field Parameter, but it only switches the first column, others don't change.
Also tried SWITCH measure approach, still dynamic column switching is not happening.
Question:
How can I dynamically switch columns between Week / Period / Date inside one Matrix using slicer?
Any working other than Dax /Field Paramter or setup suggestion will help.
Screenshots attached.
Thanks! 🙏
Last 5 Weeks (Matrix)
+--------------------------------------------+
| Process Status | In Process | Shipped | ... |
| Customer A | 12000 | 8000 | ... |
| Customer B | 7600 | 9000 | ... |
| Customer C | 14200 | 7500 | ... |
| ... |
----------------------------------------------|
Column: Actual Goods Issue Fiscal Week
Slicer: Last 5 Weeks (Selected)
Last 2 Fiscal Periods
+----------------------------------------------+
| Process Status | In Process | P&H | Shipped |
| Customer X | 15000 | 6000 | 7200 |
| Customer Y | 13000 | 4500 | 5600 |
| Customer Z | 10000 | 3900 | 4980 |
| ...
------------------------------------------------|
Column: Actual Goods Issue Fiscal Period
Slicer: Last 2 Fiscal Periods (Selected)
Last 30 Days (Date View)
+--------------------------------------------------+
| Process Status | 2025-11-03 | 2025-11-04 | ... |
| Customer 1 | 5000 | 7500 | ... |
| Customer 2 | 2900 | 6100 | ... |
| Customer 3 | 8100 | 5300 | ... |
| ... |
----------------------------------------------------|
Column: Actual Goods Issue Date
Slicer: Last 30 Days (Selected)
Hi HimanshuB,
So you are trying to switch between three completely different SAP fields (not time hierarchies from one dimension) am I right?
Here is Some Approaches That should work for you:
First Approach : Create Unified Date Dimension (This the simplest and Recommended Approach)
DateTable = DISTINCT ( SELECTCOLUMNS ( FactTable, "Date", [Actual Goods Issue Date], "FiscalWeek", [Actual Goods Issue Fiscal Week], "FiscalPeriod", [Actual Goods Issue Fiscal Period] ) )Then:
- Use DateTable[Date] as your primary date field
- Build relationships DateTable[Date] → FactTable[Actual Goods Issue Date]
- Use field parameter with DateTable[Date] , DateTable[FiscalWeek] , DateTable[FiscalPeriod]
Second Approach : Use Calculation Groups (This is More Advanced Approach)
-- Expression 1 (Date): CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP('FactTable'[Actual Goods Issue Date], 'DateTable'[Date]) ) -- Expression 2 (Week): CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP('FactTable'[Actual Goods Issue Fiscal Week], 'WeekTable'[Week]) ) -- Expression 3 (Period): CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP('FactTable'[Actual Goods Issue Fiscal Period], 'PeriodTable'[Period]) )So You need to:
- Create Date Mapping Table in Power Query that links Date ↔ Week ↔ Period
Build Relationships to your fact table
Create Field Parameter from the mapping table columns
Use Slicer on the field parameter for grain selection
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
7 Replies
Hi HimanshuB,
The reason your Field Parameter only switches the first column is usually due to Matrix setup and column context not the parameter itself...So you should :
Use a single Date table with all your time hierarchies (Date/FiscalWeek/FiscalPeriod)
- Then Create a Field Parameter with these three columns and place only this parameter in the Matrix Columns well (No other column should be at the same level)
Create a dynamic measure that reads the selected grain and respects the current column context using VALUES() :
Dynamic Goods Issue = VAR Grain = SELECTEDVALUE('Time Grain'[Time Grain]) RETURN SWITCH( TRUE(), Grain = "FiscalWeek", CALCULATE([Base Goods Issue], VALUES('DateTable'[FiscalWeek])), Grain = "FiscalPeriod", CALCULATE([Base Goods Issue], VALUES('DateTable'[FiscalPeriod])), Grain = "Date", CALCULATE([Base Goods Issue], VALUES('DateTable'[Date])), BLANK() )Note:
- Replace [Base Goods Issue] with your actual measure
- VALUES() ensures the Matrix respects the selected column for each row
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.Hi Ahmed-Elfeel : Thank you for Responding . Let me check i'll reply you asap.
Can you please show the model?
A field parameter is the righe choice but something is preventing you from using it with good results
FB
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi Ahmed-Elfeel :
Thanks for your suggestion.
I tried the Field Parameter + Dynamic Measure logic but it still gives composite key error and does not switch correctly.For clarification:
Last 30 Days → based on Actual Goods Issue Date (SAP field)
Last 5 Weeks → based on Actual Goods Issue Fiscal Week (SAP field)
Last 2 Fiscal Periods → based on Actual Goods Issue Fiscal Period (SAP field)
👉 These 3 logics are already created in SAP HANA, not calculated using DAX.
My goal:
✔ merge all into one matrix and switch columns (Date / Week / Fiscal Period) using slicer.Question:
🔹 Is it possible to dynamically switch using existing SAP fields?
OR
🔹 Do I need a unified date dimension/calculated mapping to make dynamic switching work?Looking for the best approach. Thanks.
Hi HimanshuB,
So you are trying to switch between three completely different SAP fields (not time hierarchies from one dimension) am I right?
Here is Some Approaches That should work for you:
First Approach : Create Unified Date Dimension (This the simplest and Recommended Approach)
DateTable = DISTINCT ( SELECTCOLUMNS ( FactTable, "Date", [Actual Goods Issue Date], "FiscalWeek", [Actual Goods Issue Fiscal Week], "FiscalPeriod", [Actual Goods Issue Fiscal Period] ) )Then:
- Use DateTable[Date] as your primary date field
- Build relationships DateTable[Date] → FactTable[Actual Goods Issue Date]
- Use field parameter with DateTable[Date] , DateTable[FiscalWeek] , DateTable[FiscalPeriod]
Second Approach : Use Calculation Groups (This is More Advanced Approach)
-- Expression 1 (Date): CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP('FactTable'[Actual Goods Issue Date], 'DateTable'[Date]) ) -- Expression 2 (Week): CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP('FactTable'[Actual Goods Issue Fiscal Week], 'WeekTable'[Week]) ) -- Expression 3 (Period): CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP('FactTable'[Actual Goods Issue Fiscal Period], 'PeriodTable'[Period]) )So You need to:
- Create Date Mapping Table in Power Query that links Date ↔ Week ↔ Period
Build Relationships to your fact table
Create Field Parameter from the mapping table columns
Use Slicer on the field parameter for grain selection
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
Hi HimanshuB ,
Thank you Ahmed-Elfeel for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.
