Forum Discussion

Sileye's avatar
Sileye
Regular Visitor
4 years ago

Relations between dimension table

I have a fact table and several dimension tables where one column is common to all these tables. I use a slicer to filter the data in the fact table but I also want to filter the values in the dimension tables.

 

For example, a row will not have the same name depending on the filter value selected.

In the modeling, I tried to establish the necessary links but they lead to indirect relationships.

 

Do you have any ideas on how to achieve this?

 

Thanks in advance!

 

Need Help

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    A simple way is to use a measure to filter other dimension tables based on a selection. The model should have relationships between each dimension table and the fact table in a one-to-many type relationship (in other words, avoid bi-directional and do not create relationships between dimension tables).

     

    Create a measure to use as a filter:

     

    FIlter Dim Tables =
COUNTROWS ( RELATEDTABLE ( 'Fact Table' ) )

     

    Add this measure to as a filter in the filter pane for each dimension table and set the value to "greater or equal" to 1.

    • Sileye's avatar
      Sileye
      Regular Visitor
      4 years ago

      Thanks for your answer PaulDBrown ,

       

      In fact the dimension tables I want to filter have no visual (so can I still use a measure?), they just allow to dynamically determine the name of the rows and columns of my fact table.

       

      In this case, I want to use the DOC table in a slicer to filter data in my fact table but also the COL IDs which will determine the column names of the data according to the chosen DOC.

       

      DOC
      X
      Y

       

      COLDOCID
      AX1
      AY2

       

      I can do this by linking the DOC table to these other tables but obviously could not create a relationship between the other dimension table (COL - top -) and my fact table (bottom) as shown in the example below

       

       

      Thanks,

      Sileye

       

       

       

       

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Sorry, I'm not following. Do you only have one dimension table?

    • Sileye's avatar
      Sileye
      Regular Visitor
      4 years ago

      I have several dimension table which are linked with my fact table
      and there is one (DOC) that should allow me to filter the fact table but also the other dimension tables using a selection with the column ID_DOC (present in all these tables).

       

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Ok, so create the measure I posted in my first reply and use it to filter all the slicers.

    BTW, you have many-to-many relationships which should be avoided