column limit
1 Topic
Dynamically limit output columns in matrix
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 _resultSolved1.3KViews0likes8Comments