Display dynamic date range based on field parameter selection
Hello community, Client has a column chart in which they want to display a selected date grain (field parameter called 'Grain Selector'[Grain]) on the x-axis, and a selected measure (field parameter called 'Measure Selector'[Measure]) on the y-axis. Grain has 3 values: Year, Quarter, and Month. Measure has 2 values: Total Profit, and Total Units When they select Grain = Year, they want to show the measure for a set number of display years, say 1 When they select Grain = Quarter, they want to show the measure FOR A DIFFERENT NUMBER of display quarters, say 3 Ditto for Grain = Month I tried the following DAX measure (between rows of ***): *** DynamicRangeMeasure = // Config periods to display for each grain VAR _DisplayYears = 1 VAR _DisplayQtrs = 3 VAR _DisplayMnths = 6 // Grab FParameter selections VAR _SelGrain = SELECTEDVALUE('Grain Selector'[Grain]) VAR _SelMeasure = SELECTEDVALUE('Measure Selector'[Measure]) // Compute results VAR _Year = CALCULATE( _SelMeasure, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -_DisplayYears, YEAR ) ) VAR _Quarter = CALCULATE( _SelMeasure, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -_DisplayQtrs, QUARTER ) ) VAR _Month = CALCULATE( _SelMeasure, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -_DisplayMnths, MONTH ) ) VAR _ChosenMeasure = SWITCH( _SelGrain, "Year", _Year, "Quarter", _Quarter, "Month", _Month ) RETURN _ChosenMeasure *** I keep getting error: MdxScript(Model)(13, 17) Calculation error in measure...: Column [Grain] is part of composite key, but not all columns of the composite key are included in expression... Given my business objective above, how can I fix this measure?