Dynamic Matrix Column Switch (Week → Period → Date) using Slicer Not Working

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