Data Modelling Between two Tables with Complex Relationship

Hi All,

I have two tables Product and Sales. The relationship between two tables all like below ( In Sql)

Select *from Product P
Inner Join Sales S
On S.SalesProductID = P.ProductID
and P.TransactionDate Between S.SalesValidFromDate and S.ValidToDate

While Modelling tables in Power BI/SSAS Tabular How do we specify the above relationship between these tables. I Couldn’t find any option to write conditions/expressions in the Relationship options provided. Any suggestions much appreciated. ( I have come across calculated tables is that only way to achieve this scenario?)

@baijumohan

I’m not sure I fully understand the problem but before you get to the “create a relationship” stage you need to gather, transform and finally load your data using Power Query.

In Power Query, query folding can take place (here is an article on that).
https://exceleratorbi.com.au/how-query-folding-works/

Clear illustration of join types in Power Query:

Furthermore I must admit I don’t know the first thing about SQL but looking at the code it doesn’t seem to hard. Looks like you need at least 2 dimension tables (Product, Date) and one fact table (Sales).

Sam offers a great module on data modeling, you can find it here:

Once your model is set up correctly any filters or conditions can be applied though the filter context in your report (see link below for more information on that) and/or directly in your DAX calculations.

I hope this helps you on your way…

1 Like

You basically have a dimension table being the Product table and a fact table being the Sales table.

In Power BI to create a relationship between the One side (Product) and many side fact (Sales) you need to drag the ProductId field in the Sales Table up to the ProductID field in the Product Table. This can be done in the model window. Here is a typical model setup with the relationships shown in the model view below.

1 Like

Hi Garry,

Thanks. I am actually looking for the options to write expressions in the relationship as the tables i have in my model dont have straight forward joins.

Thanks Melissa.

i will look into the power query options.

@baijumohan so you have a sales fact table and a product dimension table…the query filters the transactions…so what are the non-straightforward joins you are referring to?

Are you able to supply a diagram of your model proposal?

In Power BI you just drag the Foreign key in the sales table (ProductID) to the product id key in the Product table from the MODEL view. This establishes the one to many relationships the for ProductID to Sales Table ProductID’s. Please see this link here