Dynamic Slicer List Filtering

[josecruz50]

Hi guys,

I have a report that has a dynamic data set that updates daily. This data set captures data about a fixed amount of days. I have added two slicers to my report, one that I will call the month slicer list, and the other the day slicer list. The first one has all 12 months of the year and the other updates to the full days of the month based on the target/select month. So, my users can make their month/day combo selections without any problem and the report updates accordingly without any issues.

But, I want my month and day list selections to be filtered, based on the date periods of my data. For example, if my data set has data from 07/15/2018 to 09/30/2018, I want my month slicer list to show July, August, and September ONLY; I do no want the full list of 12 months (Jan to Dec.) The same thing goes true about the day slicer list. If my user select July, I want my day slicer lis to to show 15 to 31, because I do not have any data before July 15th.

I know that I can manually filter my slicers, like manually selecting July, August, and September, BUT remember that my data is dynamic, so those will not be valid selections all the time. For example, on October 1st, 2018, my month slicer list should show July, August, September, and October; and the day slicer list should not show July 15th anymore and show October 1st. My filters must be as dynamic as my data. If a new month and day shows up in the data set, the filter should update accordingly; the same thing goes for dates that are no longer valid.

I have been Google'ing for a solution for hours, and I have found no solution. Anyone?

Thanks,

José

[Ashish_Mathur]

Hi,

You should build a Calendar Table by going to Modelling > New Table: CALENDAR = Calendar(MIN(Data[Date]),MAX(Data[Date]))

You then build a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  In th Calendar Table, extract Year and month by using the following calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  Your slicers should be built from the Calendar Table.

Hope this helps.

[v-yuta-msft · Accepted solution]

@josecruz50 ,

So your date is not continuous and you only want to select the date which is in the date column, right? You can change the slicer mode with "List" like below:

 

If you need a slicer which only filter the month, you can remove the year, quarter and day part in the date hierarchy or create an additional calculate column using DAX: Month = MONTH(Table[Date]), then create slicer based on the new column.

If your requirement is to only show the previous 3 month dates in the slicer, you can use visual level filter(relative filter) instead. Please refer to: https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter

Community Support Team _ Jimmy Tao

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