How to suppress values in slicer that are not in the fact table

Question

I am still learning dax and sometimes use the below code when creating a new calendar table to use in my data model.

Calendar =
Var_Calendar = 
CALENDAR("1/1/2015","31/12/2022")
Return
ADDCOLUMNS(
_Calendar,
"Year",YEAR([Date]),
"MonthNumber",MONTH([Date]),
"Month",FORMAT([Date], "mmm"),
"Quarter", QTR" & FORMAT([Date], "Q"),
"MonthYearNumber", FORMAT([Date], "yy mm"),
"Month Year",FORMAT([Date], "mm yyyy")
)

As you can see at the top of the code, that date ranges from 1/1/2015 - 31/12/2022, which can be changed at any point.

When I use this in a slicer, if there is no data on a certain date in any other data tables linked to this calendar, the dates still show in a slicer, despite there being no data

Is there any alterations that can be made or alternative code to use that can still allow me to us what I need for, but only show the date in graphs or slicers that actually has data.

Answer 1

Yes, do the following.

I have your calendar table and my fact table looks like this:

They have the usual 1-to-many single direction relationship.

What you're currently seeing:

In order to see this instead:

Create a measure as follows:

Fact Count = COUNTROWS('Fact') 

Add the measure as a filter to the slicer as follows: