Forum Discussion

joelsherman's avatar
joelsherman
Frequent Visitor
3 years ago

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?

 

DAX
DAX SWITCH
Dynamic date display
Field parameters

3 Replies