Set RelativeDate Slicer Anchor Based on Another Date Slicer

[MCassady]

Hello,

Is there a way to set the anchor date of a relative date slicer, based on another date slicer?

My thought is to have 2 graphs, one showing hourly data for the selected day, and a second one showing daily data for the last 28days (from the selected date).

I tried syncing slicers, however, it changes the type of slicer to match eachother (dropdown vs relative date).

Here is a link to a sample, I have also tried to explain the problem more thoroughly in the pbix.

https://drive.google.com/file/d/1qE8_0l-Y3Krgt9y5xojJKfV5DvS-yEAv/view?usp=sharing

[v-rzhou-msft · Accepted solution]

Hi @MCassady ，

Here I suggest you to add a new dimdate table in your report. In my report I create DimDate2 based on DimDate.

DimDate2 = DimDate

Then use what if parameter to create a last N table for slicer.

Relationship is as below.

Measures:

Value1 =
CALCULATE (
    SUM ( FactData[Valueused_Standardized] ),
    FILTER ( FactData, FactData[DimDateMinute] = MAX ( DimDate[DateTime] ) )
)

Value2 = 
VAR _SELECTDATE =
    SELECTEDVALUE ( DimDate[DateDay] )
VAR _LASTDAY =
    SELECTEDVALUE ( 'Last N Day'[Last N Day] )
RETURN
    CALCULATE (
        SUM ( FactData[Valueused_Standardized] ),
        USERELATIONSHIP ( DimDate2[DateDay], FactData[Datevalue] ),
        FILTER (
            DimDate2,
            DimDate2[DateDay] >= _SELECTDATE - _LASTDAY + 1
                && DimDate2[DateDay] <= _SELECTDATE
        )
    )

Keep the interaction of all visual by default. Add measure [Value1] in graph1 and measure[Value2] in graph2. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MCassady]

Hey @v-rzhou-msft 

I tried applying this solution to the full version of my project and I received this error.

In my full version, the date tables are down to the minute, as my data could be down to that level of granularity. Do you think that this is causing an overjoin somewhere?

[MCassady]

Hey @v-rzhou-msft , thanks for the response. I will try this solution this morning.

Do you know if there is a way to do this without duplicating the DimDate table? It is a pretty large table, so I'd like to avoid duplicating it.

I was thinking that maybe I could do a measure that spits out the series of dates (selecteddate-28days to selecteddate). Then from there maybe I can slap it on the X axis, and not even worry anymore about the 2nd date slicer.

Do you think this is doable? I haven't done anything with returning a date series from a measure before, so I'm not sure it is even possible.