Dependent date slicers

[Raymo3u99]

I have a requirement to display data on two tabs base on dates selected. However the date slicer on the first should determine the selected values in the second slicer.

My date slicer has a list of first of the month dates eg 1/1/2020, 2/1/2020, 3/1/2020, 4/1/2020, 5/1/2020 etc.

When the user selects any month on this slicer, the date slicer on the second tab should select all the dates upto the date selected on the first slicer.

Example if 3/1/2020 is selected on the first slicer, then 1/1/2020, 2/1/2020 and 3/1/2020 should get selected on the second slicer on the second tab. 

Thanks in advance for your help.

Raymond

[Raymo3u99]

@v-jingzhang 

Thank you. That worked for me.

[v-jingzhang · Accepted solution]

Hi @Raymo3u99 

Per my understanding, to realize your expectation, the selected date value on tab A should be passed to tab B, and these two date slicers on the two tabs have different date ranges selected. I think up with a solution to realize this with two calendar tables as well as sync slicer function but I'm not sure whether this is applicable in your data model. You may take a try. Steps are as below.

1. Create two calendar tables: Calendar A and Calendar B.

2. On page 1, create a date slicer with 'Calendar A'[Start of Month] as field. Copy the date slicer to page 2 and sync these two slicers as below. Hide the slicer on page 2.

3. Create measures:

Selected Date = SELECTEDVALUE('Calendar A'[Start Of Month])

Measure = IF(MAX('Calendar B'[Start Of Month])<=[Selected Date],1,0)

4. On page 2, create a new date slicer with 'Calendar B'[Start of Month] as field. Add Measure into this slicer's visual filter and set value is 1. 

5. The values may not be selected automatically, so I show "Select all" option in the slicer for user to select all the dates before the date selected on page 1. 

You can download the PBIX, hope this helps.

Best Regards,

Community Support Team _ Jing Zhang

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

[skashifz]

@v-jingzhang The pbix you attached here is no more there. I have same issue, and need to implement a similar thing. Do you still have the pbix handy with you ?

[v-jingzhang]

@skashifz Sorry I didn't store it in the local storage so I don't have a copy of it now. You can follow the steps in my first reply to try it. If it doesn't work, you can leave your question here and I will look into it. Or you can also create a new topic then more people will discuss on it. There may be some better ideas and solutions.

[Anonymous]

This is an awesome solution.  I am a newbie in Power BI as having worked with Tableau for the last 9 years.  One question I have is the measure:

Measure = IF(MAX('Calendar B'[Start Of Month])<=[Selected Date],1,0)

I can't quite get how it is acting like a row level calculation in the way it is being used.  One part of me thinks if Dec is selected on Calendar A only then would values show for Calendar B since the Max of Calendar B is Dec.  Is there a way to explain this to help me understand why it is acting as a row level calc?

[v-jingzhang]

@Anonymous 

In Power BI, DAX expressions are evaluated in its evaluation context. In this date slicer, the measure is evaluated in every single row, so the MAX one is just the only value in the row. You could also replace MAX() function with MIN() or SELECTEDVALUE(), the result is the same in this slicer. But if you put a DAX expression in other visuals, the result will probably change because its evaluation context is changed.

When you first get in touch with DAX, it may be a little difficult to understand the evaluation context. Here are some articles about how to understand context in DAX. There are also some functions you could use to change the context manually.

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://adatis.co.uk/understanding-dax-through-the-power-of-evaluation-context/

The best way to learn DAX is to create your own measures and columns with various functions and see the differences and changes between them. Hope you will enjoy the tour with Power BI.

[Anonymous]

Thank you for framing that up so nicely!

[amitchandak]

@Raymo3u99 , I doubt that is possible, You can show the date of that month, but might be able to show more than what you have selected in the other slicer