Forum Discussion

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

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.

tab.pbix135 KB

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.

tab.pbix135 KB

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

So, what are the possible ideas for this?

Thanks in Advance.