Date Slicer in PowerBI

Question

So I have 5 Data tables that are linked to a main Calendar table. The main Calendar table is set up with date from Jan 2020 to Dec 2022. My date slicer is from Calendar table.

I have a page dedicated for each of the 5 Data tables.

Here's my problem: Some Data tables have data in 2020 and some don't have any starting Q4 of 2020. Since my date slicer is coming from Calendar table, it's showing 2020 options (for example Q1 Jan 2020) even if there's no data there.

How do I hide date selections in my date slicer with no data in it?

TIA

Answer 1

Okay I figured this out from doing research in powerBi. Here's the link: https://community.powerbi.com/t5/Desktop/Limit-Timeline-Slicer/m-p/42850

Basically, you turn the Date slicer to a table and exclude dates you don't want to see.

Then you change the Date slicer back to a slicer and you won't see the excluded dates anymore.

Answer 2

In this scenario I would recommend using a simple, performant measure as a filter on the visual. For instance, if one of your data tables are [Data table 1]...[Data table 5], you can create a measure that counts each one:

[Count Data Table 1] = COUNTROWS('Data Table 1')

Then, when you have your slicer selected, you can expand the filter pane blade and add your measure under the "Filters on this visual". Set the filter to greater than 0 and it will automatically update the slicer as your data changes.

Note: Using a measure as a filter is only possible on visuals as measures need context to execute. This approach will not work if you plan on putting your date filters in the filter pane, or would like to apply the filter to the whole report page.