Forum Discussion
Power bi model with multiple joins
Help required on Power bi model with multiple joins
fact_sales has business date and product id
dim_product table has business_date and product id how we can join on power bi model ?
Hi Anonymous ,
In Power BI, the correct approach is to maintain a star schema where the dim_product table contains only product-related attributes and does not include business_date, as dates should be handled separately in a dim_date table. The fact_sales table contains both business_date and product_id, allowing it to connect to both dimension tables. To achieve this, create a dim_date table using the CALENDAR function in DAX:
dim_date = CALENDAR(MIN(fact_sales[business_date]), MAX(fact_sales[business_date]))Next, establish relationships where dim_product[product_id] connects to fact_sales[product_id] in a one-to-many relationship, ensuring that product attributes can filter sales data. Similarly, dim_date[business_date] should connect to fact_sales[business_date], enabling time-based analysis. With this setup, dim_product provides product-level filtering, while dim_date supports date-based aggregations, ensuring a clean and efficient Power BI data model.
Best regards,
2 Replies
- bhanu_gautam
Super User
Anonymous Create a relationship between fact_sales[product_id] and dim_product[product_id].
Create another relationship between fact_sales[business_date] and dim_product[business_date].Ensure that the cardinality of the relationships is set correctly. Typically, fact_sales will have a many-to-one relationship with dim_product on both product_id and business_date.
But only one will be active relation
- DataNinja777
Hi Anonymous ,
In Power BI, the correct approach is to maintain a star schema where the dim_product table contains only product-related attributes and does not include business_date, as dates should be handled separately in a dim_date table. The fact_sales table contains both business_date and product_id, allowing it to connect to both dimension tables. To achieve this, create a dim_date table using the CALENDAR function in DAX:
dim_date = CALENDAR(MIN(fact_sales[business_date]), MAX(fact_sales[business_date]))Next, establish relationships where dim_product[product_id] connects to fact_sales[product_id] in a one-to-many relationship, ensuring that product attributes can filter sales data. Similarly, dim_date[business_date] should connect to fact_sales[business_date], enabling time-based analysis. With this setup, dim_product provides product-level filtering, while dim_date supports date-based aggregations, ensuring a clean and efficient Power BI data model.
Best regards,
