Forum Discussion
How to left join a table based on IF condition
Probably LOOKUPVALUE() or TREATAS() will get you the proper filters to get the data. Have you tried these?
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
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.
