Forum Discussion
Relationship with Fact tables
Hi
I have duplicate values in a fact table but these values are linked to different categories
prod_id | category subcategory
233 Outdoor wear Coat
233 Outdoor wear Long Coat
233 Ski wear Thermal Coat
I have prod_id only put in once in another table with different fields such as
prod_id | Currency | Price
233 Euro 300.00
244 Dollars 400.00
I want to be able to match product ID 233 with the above values and display them as follows in a table:
prod_id | category subcategory |Currency |Price
233 Outdoor wear Coat Euro 300.00
233 Outdoor wear Long Coat Euro 300.00
233 Ski wear Thermal Coat Euro 300.00
This is just fictional data obviously but I'm not sure how to get to this point, where there are duplicate values.
Thanks in advance.
Hi,
In a dimensional model, it is generally recommended **not to create a direct relationship between two fact tables**. Instead, both fact tables should share common dimension tables (such as Date, Customer, Product, or Location) in a star schema. This approach keeps the model simpler, avoids ambiguous filter paths, and usually provides better performance.
If the two fact tables need to be analyzed together but don't share the same grain, consider using shared dimensions or, where appropriate, a bridge table instead of creating a direct fact-to-fact relationship.
Could you share:
* The structure of your two fact tables.
* The columns you are trying to relate.
* The business scenario you're trying to achieve.That will help the community recommend the most appropriate modeling approach.
6 Replies
Hi Lauraeire_81,
If prod_id is unique in the Price table and repeated in the Category table, create a 1-to-many relationship.
Product[prod_id] (1) ----> (*) Category[prod_id]
Then use fields from both tables in the visual. Power BI will automatically repeat the Currency and Price values for each matching category/subcategory row of the same prod_id.
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics EngineerHi thank you for all your suggestions. One of the tables mustn't have had unique values in the primary key field that's why I wasn't able to create a relationship for. However, I did a workaround with the Dimension table Kiran-7312 which helped remove all duplicates. I also just split the data out into two reports as it looked better that way anyway aesthetically. Thank you all for your help with this.
Hi Lauraeire_81,
This is exactly what a relationship handles — the duplicates in your first table are fine, because matching only requires unique values on one side, and your price table has that.
In Model view, drag prod_id from the price table onto prod_id in the category table. Power BI creates a One-to-many relationship (price table = one side, category table = many side). Leave the cross-filter direction on Single.
Then build your table visual with category and subcategory from the first table, plus Currency and Price from the price table. Each row picks up its matching currency and price through the relationship — giving exactly the three-row output you sketched, with 300.00 repeated on every 233 row. One tip: set Price to Don't summarize (dropdown on the field in the visual's field well), since here it's a product attribute you want shown per row, not a number to add up.
If you'd rather have the columns physically inside the category table instead, a calculated column with RELATED does the same thing: Currency = RELATED('Prices'[Currency]). It follows the same relationship. But for a display table, the relationship alone is all you need — no DAX required.
The mental model: your price table is acting as the dimension here (unique prod_id = the "one" side), and duplicates on the many side aren't a problem — they're the whole point of the relationship.
🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist
💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.Hi,
In a dimensional model, it is generally recommended **not to create a direct relationship between two fact tables**. Instead, both fact tables should share common dimension tables (such as Date, Customer, Product, or Location) in a star schema. This approach keeps the model simpler, avoids ambiguous filter paths, and usually provides better performance.
If the two fact tables need to be analyzed together but don't share the same grain, consider using shared dimensions or, where appropriate, a bridge table instead of creating a direct fact-to-fact relationship.
Could you share:
* The structure of your two fact tables.
* The columns you are trying to relate.
* The business scenario you're trying to achieve.That will help the community recommend the most appropriate modeling approach.
Hi Lauraeire_81
You can follow the below stpes to get your result.
If you have created a relation between these two table ( fact table & dim_table) using prod_id (1---*)
Just create a table visual and drag the
prod_id (dim_table)
Category fact table
sub-Category fact table
Currency (dim_table)
Price (dim_table)If this answers your questions, kindly accept it as a solution and give kudos.
Hi Lauraeire_81,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
