CODEWITHSUNDEEP
powerbipowerbi-desktopssas-tabular
bmsqldev
bmsqldev

Reputation: 2735

Complex Relationship with Multiple Conditions

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

Upvotes: 0

Views: 1279

Answers (2)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

Here is an alternative approach.

No calculated table is necessary. Create a relationship between Product and Sales using ProductID, and define a measure to aggregate Sales within valid period.

Product Sales =
SUMX(
    'Product',
    CALCULATE(
        [Total Sales],
        Sales[SalesValidFromDate] <= 'Product'[TransactionDate]
          && Sales[SalesValidToDate] >= 'Product'[TransactionDate]
    )
)

Upvotes: 1

Karl Anka
Karl Anka

Reputation: 2849

As mentioned in the comments you will not be able to define that relation in the Power BI model pane. You can create a calculated table like this though:

join_table =
FILTER (
    CROSSJOIN ( 'Product'; Sales );
    'Product'[Prod_ID_p] = Sales[Prod_Id] 
        && 'Product'[Trans_Date] >= Sales[Sales_Valid_From]
        && 'Product'[Trans_Date] < Sales[Sales_Valid_To]
)

CROSSJOIN will complain if there are columns with the same name in both tables, why it's renamed to Prod_ID_p.

Upvotes: 1

Related Questions