Forum Discussion

MCassady's avatar
MCassady
Icon for Helper I rankHelper I
4 years ago
Solved

Set RelativeDate Slicer Anchor Based on Another Date Slicer

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      MCassady
      Icon for Helper I rankHelper I
      4 years ago

      Hey Anonymous , 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.

    • MCassady's avatar
      MCassady
      Icon for Helper I rankHelper I
      4 years ago

      Hey Anonymous 

       

      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?