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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
handek
New Member

How can I add dimension tables with dates to my model?

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?

 

 

1model.jpg1tablebeforestatus.jpg1tableafterstatus.jpg

1 REPLY 1
Chris_White
Resolver II
Resolver II

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.