Slicer element and date dimension

[meyer]

Hi everybody,

Here's my data model :

There is several joins between my fact table and my dimensions ones. The first one is between GRP_AFFAIRE and ID_CLIENT and the others are between all the DATE_* fields and ID_DATE.

Now, I want to display a Slicer element on my dashboard that will contain the field YEAR (ANNEE in my Date dimension). The problem is that when I made my sql database, I've integrated dates from 01/01/1900 to 01/12/2100 as I wanted to have an exhaustive dimension. When I put the slicer with the year field on my dashboard, I have years from 1900 to 2100 that appears.

How can I manage to display only years that are present on my fact table (like a sort of inner join between facts and date_dim) ?

Thanks in advance

[v-yulgu-msft · Accepted solution]

Hi @meyer,

Please add a calculated column in your date dimention table. Then, add this new column to slicer, this way, the slicer will display only years that are present on fact table.

Existing Year =
LOOKUPVALUE ( myFact[Date].[Year], myFact[Date].[Year], 'Date Dim'[Year] )

Best regards,

Yuliana Gu