Power bi model with multiple joins

[Anonymous]

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 ?

[DataNinja777 · Accepted solution]

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,

[bhanu_gautam]

@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