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 dimens...
Need Help
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