Parameter on CALCULATETABLE not working

[Anonymous]

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

[OwenAuger · Accepted solution]

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

[Anonymous]

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