Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
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
Proud to be a Super User!
Paul on Linkedin.
Sorry, I'm not following. Do you only have one dimension table?
Proud to be a Super User!
Paul on Linkedin.
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).
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.
Proud to be a Super User!
Paul on Linkedin.
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 |
COL | DOC | ID |
A | X | 1 |
A | Y | 2 |
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
User | Count |
---|---|
84 | |
79 | |
63 | |
62 | |
51 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |