Forum Discussion
Using reference dimension as a slicer with multiple fact tables
We have a dimension [Product] which is present in three fact tables [FactA], [FactB] and [FactC]. Currently we have referenced the dimension multiple times [ProductFactA], [ProductFactB] and [ProductFactC] and linked it as required to each fact. Each dimension is identical, so we would like to use a couple of the fields in the [Product] dimension as a couple of slicers, but to slice across all three fact tables at the same time, for example Product Name and Product Category.
Is there a best practice way of doing this?
4 Replies
Thanks for the response.
To my knowledge, it won't work if you only have one product table unles there is something I am missing?I wouldn't be able to link it to all three using the same field ProductKey, not with active links. Each of the three fact tables can be used separately and they are detail fact records from the main fact table, which is like a header.
In our world, fact is a row that defines a project and fact A/B/C are different types of associated items against that project which all have product names which are linked by ProductKeys on all three tables. On the reference Product table, there is a ProductKey, Product Name and Product Category. But on the slicer we want to add on the Product Category and for us not to have to change the table the Product Category comes from depending on which fact table we want to filter.
Hopefully that make sense?
If I understand correctly, the 'Fact' table is your header table and 'Fact2','Fact3','Fact4' are your fact details table. So what you might consider doing is merging the header table into each of the 3 details table. Or if possible, consider actually creating one fact table out of these 4 (flattening the header/detail tables). So I think it's more of a data modell fix, than a DAX fix here.
Chapter 2 of 'Analyzing data with Microsoft Power BI and Power Pivot for Excel' by Alberto Ferrari and Marco Russo go through these details of header/details tables. https://www.oreilly.com/library/view/analyzing-data-with/9781509302833/ch02.html
