Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relationship with calendar Table

Hi everyone,

I have two Fact tables that i want to relate to a Calendar calendar but when i see the values in the matriz is something wrong.
As you see in the picture one, i use a bridge table with the years to make the relationship between the two fact tables and the Calendar table (Tabla Calendario). When i see the model and slice by the year its okay, but when i slice by month its computing the same values for every single month. What i did wrong and how can i solve it?

Thanks !

General Comment
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Your fact tables and calendar table are connected with [year] column, so when filtering table on [month], it would show incorrect value.

    If you fact tables have [year] and [month] columns, you can create a  column=[year]&[month], then create [year]&[month] column in your calendar table, create relationships between them.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Your fact tables and calendar table are connected with [year] column, so when filtering table on [month], it would show incorrect value.

    If you fact tables have [year] and [month] columns, you can create a  column=[year]&[month], then create [year]&[month] column in your calendar table, create relationships between them.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago
    Hey Anonymous

    Your model is working as it should, judging on how you've configured the relationships.

    See, since what is actually filtering the tables it 'dAño', it doesn't matter if you select a month, because every month points first to the corresponding year and then data is filtered by that year. You should reconfigure the relationships, and if you have an actual date column just use this one. In my opinion, 'dAño' is not needed as a table, since you already have [año] in your 'Tabla calendario'.

    Best,
    Spyros
  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    HI Anonymous ,

     

    I think you need to make your relationships bi-directional, in-order to get the data filtered.

     

    Thanks,

    Pragati