topic Re: Parameter on CALCULATETABLE not working in DAX Commands and Tips

Parameter on CALCULATETABLE not working

Anonymous — Mon, 25 Sep 2023 00:01:22 GMT

Hello,

I am trying to create a new Calendar to be used specifically for sparklines and I want to add a control for users to define the number of days to display (__dateoffset) via numeric range Parameters. The measure works if I assign a static value to __dateoffset but not with the SELECTEDVALUE function.

I am not sure what I'm missing.

vis_SparklineCalendar =

VAR __dateoffset = SELECTEDVALUE( vis_SparklineOffset[vis_SparklineOffset] )

VAR __maxdate = max ( Orders[OrderDate] )

VAR __sparklinecalendar =

CALCULATETABLE(

'Calendar',

'Calendar'[Date] <= __maxdate

&& 'Calendar'[Date] > __maxdate - __dateoffset

)

RETURN __dateoffset

Thank you!

David

Re: Parameter on CALCULATETABLE not working

OwenAuger — Mon, 25 Sep 2023 05:01:53 GMT

Hi there Anonymous

Can you clarify what you're trying to do?

If you're trying to create a calculated table that responds to a slicer selection, that's not possible, as any table is part of the dataset and not affected by filters in any report. In this case, SELECTEDVALUE will likely return a blank value.

Also, the DAX expression in your post ends with

RETURN __dateoffset

Is it mean to be this?

RETURN __sparklinecalendar

If you want to display the sparkline only for a certain range of dates, I would recommend creating the new Calendar table for the maximum possible range, then using a measure for the sparkline that is nonblank only for the required dates.

For example, if you first create 'Sparkline Calendar' (plus set up relationships and mark it as a date table), then you could write a measure like this to limit the dates for which nonblank values are returned:

Sparkline Measure = VAR __dateoffset = SELECTEDVALUE ( vis_SparklineOffset[vis_SparklineOffset] ) VAR __maxdate = CALCULATE ( MAX ( Orders[OrderDate] ), REMOVEFILTERS () ) RETURN CALCULATE ( [Existing Measure], KEEPFILTERS ( DATESINPERIOD ( 'Sparkline Calendar'[Date], __maxdate, - __dateoffset, DAY ) ) )

Re: Parameter on CALCULATETABLE not working

Anonymous — Tue, 26 Sep 2023 00:22:04 GMT

Thank you so much!

You are correct, I meant the measure be RETURN __sparklinecalendar.

I didn't realize I was actually using a slicer to filter a table of the dataset instead of it's result. It make sense how that can result to a lot of ambiguity.

Thank you for the solution. I will give it a go!

Cheers,
David