Complex Relationship with Multiple Conditions

[baijumohan1990]

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?)

[v-frfei-msft · Accepted solution]

Hi @baijumohan1990 ,

We can create a calculated table as below in power bi.

Table = 
VAR a =
    FILTER ( 'Product', 'Product'[Product ID] IN VALUES ( Sales[Product ID] ) )
VAR c =
    ADDCOLUMNS (
        a,
        "fd", CALCULATE (
            MAX ( Sales[SalesValidFromDate ] ),
            FILTER ( Sales, Sales[Product ID] = 'Product'[Product ID] )
        ),
        "tod", CALCULATE (
            MAX ( 'Sales'[ValidToDate] ),
            FILTER ( Sales, Sales[Product ID] = 'Product'[Product ID] )
        )
    )
RETURN
    FILTER (
        c,
        'Product'[TransactionDate] >= [fd]
            && 'Product'[TransactionDate] <= [tod]
    )

For more details, please check the pbix as attached.

[MostafaFares55]

Hi @v-frfei-msft 

I have a similar case, But there is no link key between the two tables.
ONLY this condition P.TransactionDate Between S.SalesValidFromDate and S.ValidToDate

So, what are the possible ideas for this?

Thanks in Advance.