Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joelsherman
Regular Visitor

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?

 

3 REPLIES 3
lbendlin
Super User
Super User

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-...
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

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors