How to left join a table based on IF condition

[astamenenkovik]

Hello,

I hope that anyone could help me towards solving my problem.

I have two queries: PRICES and SALES. 

The Prices query contains all product prices chronologically ordered as they were changed.

I want to add a column in Sales query consisting the valid price on the date of order. 

I can't make one to many relationship, as there are many prices for the specific product.

I have created a calulated column with the following DAX code (without existing a relationship between the tables). It works fine, but the project slowed down the other calculations, as the calculated table is recalculating permanently. 

GROSS PRICE = 
VAR orderDate = RELATED('Date'[Date])
VAR productID = SALES[ARTICLE ID]
VAR ProductSize = SALES[ARTICLE SIZE]
VAR LastPriceChange = 
    MAXX (
        FILTER (
            PRICES,
            PRICES[PHDATE]<=orderDate && PRICES[PHANR]=productID && PRICES[PHGRO]=ProductSize),PRICES[PHDATE]
    )
VAR GrossPriceFilter = 
    FILTER (
        FILTER (
            ALLEXCEPT(PRICES,PRICES[PHSTAT],PRICES[PHTMST],PRICES[PHUSER],PRICES[Index]),PRICES[PHDATE]<=orderDate && PRICES[PHANR]=productID && PRICES[PHGRO]=ProductSize
            )
            ,
        PRICES[PHDATE]=LastPriceChange
    )
VAR GrossPrice = 
    CALCULATE (
        SELECTEDVALUE (
            PRICES[PHVKP]
            ),
        GrossPriceFilter
    )
RETURN
GrossPrice

I was wondering if I can add a new column in the Sales query that will get the valid prices (based on Article ID, Article Size and Price change date < Order Date), before performing any calucalation with DAX.

Many thanks for the provided help on this issue.

Aleksandar 

[lbendlin]

Probably LOOKUPVALUE() or TREATAS() will get you the proper filters to get the data. Have you tried these?

[astamenenkovik]

Hi Ibendilin,

Thanks for the provided solution. Yes, i have tried also with these formulas.

My solution in DAX works, but the issue is that the project becomes much slower, as the column recalculates everytime when the model is loaded (for example by creating a new measure).

Therefore I guess that I should initially prepare the data in Power Query with M. I tried the following code to create a column "ValidTill". It works, but the refresh time became very inefficient (450 thousend rows are in the table, after the code it refreshes more than 200 million rows ).

= Table.AddColumn(#"Added Index", "ValidTill", each if [PHANR] = #"Added Index"{[INDEX]+1} [PHANR] and [PHGRO] = #"Added Index"{[INDEX]+1} [PHGRO] then Date.AddDays(#"Added Index"{[INDEX]+1} [PHDATE],-1) else Date.From(DateTime.LocalNow()), Date.Type)

Here is my Data structure:

PRICES	SALES
Product ID	Product ID
Product Size	Product Size
Price valid from	Order Date
Price	Price (Condition:  [Price valid from]  <= [Order Date] and [Price valid from] = max ( [Price valid from] )

I need the [Price] in the Sales table.

Thanks.

Best regards,

Aleksandar

[v-zhenbw-msft]

Hi @astamenenkovik ,

Do you want to create a measure or a column?

Do you want to use Price in Sales table for other calculations, or just want to display Price in Sales table?

Could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.