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?
