Forum Discussion

HimanshuB's avatar
HimanshuB
New Member
8 months ago
Solved

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 ...
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    8 months ago

    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.
Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
8 months ago

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.

 

HimanshuB's avatar
HimanshuB
New Member
8 months ago

Hi Ahmed-Elfeel  : Thank you for Responding . Let me check i'll reply you asap.