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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dyabes
Helper I
Helper I

Parameter on CALCULATETABLE not working

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
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi there @dyabes 

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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi there @dyabes 

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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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