CODEWITHSUNDEEP
powerbicalendardaxpowerbi-desktop
Sc0719
Sc0719

Reputation: 75

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

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.

Upvotes: 1

Views: 670

Answers (2)

Peter
Peter

Reputation: 12365

You can either get the dates from another Table

CALENDAR( MIN('Table'[date]), MAX('Table'[date]) )

or you can use

CALENDARAUTO()

which does this automatically, but always creates whole years.

Upvotes: 0

davidebacci
davidebacci

Reputation: 30289

Yes, do the following.

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

enter image description here

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

enter image description here

What you're currently seeing:

enter image description here

In order to see this instead:

enter image description here

Create a measure as follows:

Fact Count = COUNTROWS('Fact')

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

enter image description here

Upvotes: 1

Related Questions