Dynamically limit output columns in matrix

[LesleyR]

I have a matrix which is dependent on date range filter and two parameter field filters (period - day, week, month; metric selection).  My goal is to limit the output values to display the last 99 periods of the selected date range where day or week is selected.  Saving the 100th column for Totals.   Tried below DAX but it is not outputting correctly.  Individual charts and bookmarks with reletive date filters dont work either since the date range is dynamic.

Is this even possible? 

VAR _period = SELECTEDVALUE(par_period[par_period Order])

    //0=day, 1=week, 2=month

VAR _metric = SELECTEDVALUE(par_metric[par_metric Order])

    //count=0, percent=1

VAR _minday = CALCULATE( MAX(dimCalendar[Date]), ALLSELECTED(dimCalendar[Date])) -98

VAR _minweekoffset = CALCULATE( MAX(dimCalendar[WeekOffset]), ALLSELECTED(dimCalendar[WeekOffset])) - 98

VAR _minsow = CALCULATE(MIN(dimCalendar[Date]), dimCalendar[WeekOffset] >= _minweekoffset)

VAR _filterdate =

    SWITCH(

        TRUE(),

        _period = 0, _minday,

        _period = 1, _minsow,

        _period > 1, MIN(dimCalendar[Date]) //will never have more than 100 months in dataset

    )

VAR _metric1 = CALCULATE([Metric 1], factData[Date] >= _filterdate)

VAR _metric2 = CALCULATE([Metric 2], factData[Date] >= _filterdate)

VAR _metric3 = CALCULATE([Metric 3], factData[Date] >= _filterdate)

VAR _result =

    SWITCH(

        TRUE(),

        _metric = 0 && _period >=0, _metric1,

        _metric = 1 && _period < 1, FORMAT( _metric2 , "0.0%"),

        _metric = 1 && _period > 0, FORMAT( _metric3, "0.0%")

    )

RETURN

     _result

[v-hashadapu]

Hi @LesleyR , We are pleased to hear that you have found a workaround. Kindly let us know if it successfully resolved your issue. If it did, please share the details here and mark them as 'Accept as solution' to assist others with similar problems. If it did not, please provide further details.
Thank you.

[v-hashadapu]

Hi @LesleyR , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[LesleyR · Accepted solution]

I have found the original DAX to work where there is a single metric, so i created two matrix tables and used bookmarks to allow for metric selection.

[v-hashadapu]

Hi @LesleyR , We are pleased to hear that you have found a workaround. Kindly let us know if it successfully resolved your issue. If it did, please share the details here and mark them as 'Accept as solution' to assist others with similar problems. If it did not, please provide further details.
Thank you.

[v-hashadapu]

Hi @LesleyR , Thank you for reaching out to the Microsoft Community Forum.

Assuming your dimCalendar has [Date] and [WeekOffset] columns, and factData is linked to it, I suggest creating a calculated table to lock in exactly 99 periods based on the selected period type. This keeps your matrix columns at 99, adapting dynamically to your date range filter. It ranks days or weeks efficiently and uses the full calendar for months.
Last99Periods =

VAR _period = SELECTEDVALUE(par_period[par_period Order])

VAR _maxDate = CALCULATE(MAX(dimCalendar[Date]), ALLSELECTED(dimCalendar))

VAR _maxWeek = CALCULATE(MAX(dimCalendar[WeekOffset]), ALLSELECTED(dimCalendar))

RETURN

SWITCH(

    TRUE(),

    _period = 0,

        SELECTCOLUMNS(

            FILTER(

                ADDCOLUMNS(

                    CALENDAR(_maxDate - 98, _maxDate),

                    "Rank", RANKX(CALENDAR(_maxDate - 98, _maxDate), [Date], , ASC)

                ),

                [Rank] <= 99

            ),

            "Period", [Date],

            "Rank", [Rank]

        ),

    _period = 1,

        SELECTCOLUMNS(

            FILTER(

                ADDCOLUMNS(

                    FILTER(dimCalendar, [WeekOffset] >= _maxWeek - 98 && [WeekOffset] <= _maxWeek),

                    "Rank", RANKX(FILTER(dimCalendar, [WeekOffset] <= _maxWeek), [WeekOffset], , ASC)

                ),

                [Rank] <= 99

            ),

            "Period", CALCULATE(MIN(dimCalendar[Date]), FILTER(dimCalendar, [WeekOffset] = EARLIER([WeekOffset]))),

            "Rank", [Rank]

        ),

    _period = 2, dimCalendar

)

For the values, I recommend a measure that handles your metric selection (count or percent) and applies the right filter for each period. It also uses the matrix’s grand total for the 100th column, formatting percentages as needed, and it’s optimized to avoid unnecessary calculations.

DynamicMetric =

VAR _period = SELECTEDVALUE(par_period[par_period Order])

VAR _metric = SELECTEDVALUE(par_metric[par_metric Order])

VAR _selectedPeriod = SELECTEDVALUE(Last99Periods[Period])

VAR _total = CALCULATE(

    SWITCH(_metric, 0, [Metric 1], 1, IF(_period < 1, [Metric 2], [Metric 3])),

    ALLSELECTED(dimCalendar[Date])

)

RETURN

IF(

    ISBLANK(_selectedPeriod),

    IF(_metric = 1, FORMAT(_total, "0.0%"), _total),

    VAR _result = CALCULATE(

        SWITCH(_metric, 0, [Metric 1], 1, IF(_period < 1, [Metric 2], [Metric 3])),

        dimCalendar[Date] = _selectedPeriod

    )

    RETURN IF(_metric = 1, FORMAT(_result, "0.0%"), _result)

)

To make it work, just set up your matrix with the table’s period column and the measure, then turn on grand totals. This gives you 99 periods plus a clean total column, all tied to your dynamic filters without extra visuals or hacks.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[LesleyR]

Hi

thanks for the suggestion, unfortunately i could not get this to work, resulting in 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value' error

[bhanu_gautam]

@LesleyR , Try using

DAX
VAR _period = SELECTEDVALUE(par_period[par_period Order])
//0=day, 1=week, 2=month

VAR _metric = SELECTEDVALUE(par_metric[par_metric Order])
//count=0, percent=1

VAR _minday = CALCULATE(MAX(dimCalendar[Date]), ALLSELECTED(dimCalendar[Date])) - 98
VAR _minweekoffset = CALCULATE(MAX(dimCalendar[WeekOffset]), ALLSELECTED(dimCalendar[WeekOffset])) - 98
VAR _minsow = CALCULATE(MIN(dimCalendar[Date]), dimCalendar[WeekOffset] >= _minweekoffset)

VAR _filterdate =
SWITCH(
TRUE(),
_period = 0, _minday,
_period = 1, _minsow,
_period > 1, MIN(dimCalendar[Date]) //will never have more than 100 months in dataset
)

VAR _metric1 = CALCULATE([Metric 1], dimCalendar[Date] >= _filterdate)
VAR _metric2 = CALCULATE([Metric 2], dimCalendar[Date] >= _filterdate)
VAR _metric3 = CALCULATE([Metric 3], dimCalendar[Date] >= _filterdate)

VAR _result =
SWITCH(
TRUE(),
_metric = 0 && _period >= 0, _metric1,
_metric = 1 && _period < 1, FORMAT(_metric2, "0.0%"),
_metric = 1 && _period > 0, FORMAT(_metric3, "0.0%")
)

RETURN
_result

[LesleyR]

HI, not sure i see what is different here, can you highlight which part of the DAX you changed