topic Re: Display dynamic date range based on field parameter selection in DAX Commands and Tips

Display dynamic date range based on field parameter selection

joelsherman — Wed, 17 May 2023 16:49:34 GMT

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?

Re: Display dynamic date range based on field parameter selection

lbendlin — Fri, 19 May 2023 01:39:40 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Display dynamic date range based on field parameter selection

joelsherman — Fri, 19 May 2023 15:33:55 GMT

Ah, thank you. I noticed they removed the attachment button (where I was going to provide my .pbix) but had no idea they're reserving this for MS or Super User. Interesting. I've uploaded the .pbix to google cloud storage here: https://drive.google.com/file/d/1JCcR88yvpOwj-p6HMSY_GdhjB16NKMwC/view?usp=sharing

Re: Display dynamic date range based on field parameter selection

lbendlin — Sat, 20 May 2023 00:33:35 GMT

That file seems to work as you intended. How can I reproduce the error?