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.Trans...
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.

     

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.