Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
elliottriley
Frequent Visitor

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.

 

model.jpg

 

Is there a best practice way of doing this?

4 REPLIES 4
Anonymous
Not applicable

What about having one 'Product' dimension table instead of 3 separate? Then you can have a relationship between one 'Product' dimension table and each fact table. 

 

I see you drew that Fact has a relationship with Fact A/B/C. Is this an actual relationship here, or?

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?

Hi @elliottriley,

 


 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.

 


Based on my understanding, there is one dimention table listing unique Products and three fact tables in which fields are formatted as such a hierarchy ProjectName -> Category -> ProductName. There existing a one to many relationship from dimention table to each fact table. That case, you just need to drag the Product Category from dimention table into slicer, the records of three fact tables will be updated according to the slicer selection.

 

If I have something misunderstood, please post sample data of the product dimention table and each fact table, including table structure and detailed records. Also, show us the desired output to make the description clearer. How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.