Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have dimension tables that include dates in my model.
For example, the status of a product changes every year. A product that is a new product in 2021 becomes an existing product in 2022.
Similarly, a customer who is not in 2022 becomes a new customer in 2023.
When I want to add the product or customer status information to a table visual, I get an error about model connection or some rows are duplicated.
How can I include these tables in my model? Is my model wrong?
Yes, your model is causing you to have duplicate rows. If your customer is in dim_CustomerStatus three times, then the sale will be counted three times unless you also filter dim_CustomerStatus to just one year. Same for product status. I think the best solution for you is to also join on date, but perhaps not in the way you're immediately thinking.
1. Create a new column in dim_ProductStatus called Product_status_ID and define it as [Product_ID] & [Date]
2. Create a new column in dim_CustomerStatus called Customer_status_ID and define it as [Customer_ID] & [Date]
3. Create a new column in FACT SALES called Product_status_ID defined as [Product_ID] & year([Date])
4. Create a new column in FACT SALES called Customer_status_ID defined as [Customer_ID] & year([Date])
5. Remove your existing joins: dim_Product -> FACT SALES <- dim_Customer.
6. Join dim_Product -> dim_ProductStatus -> FACT SALES <-dim_CustomerStatus <- dim_Customer. Joins to FACT SALES use the new columns you made in steps 1-4. Other two joins are as they are now.
In general, model issues are caused by tables not having a unique identifier (i.e. an ID column which uniquely idenfies each row). Two of your tables did not have such a column, so I've added one.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
81 | |
53 | |
37 | |
35 |