Forum Discussion

meyer's avatar
meyer
Regular Visitor
8 years ago
Solved

Slicer element and date dimension

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

 

 

  • 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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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