Forum Discussion

baijumohan1990's avatar
baijumohan1990
Helper II
6 years ago
Solved

Complex Relationship with Multiple Conditions

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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's avatar
      MostafaFares55
      Regular Visitor
      3 years ago

      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

      S      o, what are the possible ideas for this?

      Thanks in Advance.