Extracing Column from foreign table with conditions

[Cary_Casey]

Hello,

There are two tables that do not have a direct relationship Result and supply. I am trying to extract column supply from table supply into result based on certain conditions.

Result

location	date	Result	Product
one	11/15/2019	Y	1
one	11/15/2019	N	2
two	11/17/2019	Y	1
two	11/17/2019	Y	2
three	12/3/2019	N	1
three	12/3/2019	Y	2
one	1/5/2020	Y	1
one	1/5/2020	Y	2
three	1/5/2020	Y	1
three	1/5/2020	Y	2

Supply

location	date	supply	product
one	10/11/2019	A	1
one	10/10/2019	A	1
one	10/5/2019	D	2
one	10/6/2019	D	2
two	11/15/2019	C	1
two	11/12/2019	C	1
two	11/10/2019	C	2
three	12/1/2019	A	1
three	11/30/2019	A	1
three	11/29/2019	A	2
one	12/30/2019	B	1
one	12/31/2019	B	1
one	1/1/2020	B	2

Desired outcome Result with supply column from supply

location	date	Result	Product	Supply
one	11/15/2019	Y	1	A
one	11/15/2019	N	2	D
two	11/17/2019	Y	1	C
two	11/17/2019	Y	2	C
three	12/3/2019	N	1	A
three	12/3/2019	Y	2	A
one	1/5/2020	Y	1	B
one	1/5/2020	Y	2	B
three	1/5/2020	Y	1	A
three	1/5/2020	Y	2	A

Establishing a many to many relationship on location ends up duplicating the rows in results table if there is more than one result in the supply column from table supply. The next attempt was the following DAX equation :

Supply = IF(Result[date] > Supply[date] && Result[location] = Supply[location], CALCULATE(MAX(Supply[supply]),FILTER(Supply,MAX(Supply[date]))), 0)

This does not work as Power BI can't find a single entry for each of the supply columns defined in the logical test(s) of the IF statement.

This goal is to get the most recent supply before the date of the Result table for a result for a location.

[VasTg]

@Cary_Casey 

Try this as a new column in Result table..

Column = CALCULATE(MAX(Supply[supply]),FILTER(ALL(Supply),Result[location]=Supply[location] && Result[Product]=Supply[product] && Supply[date]<=Result[date]))

Feel free to modify the conditions.

If it helps, mark it as a solution

Kudos are nice too

[Cary_Casey]

@VasTg 

After doing some experiments with this calculated column, the suggested DAX formula does not give the solution I was seeking.

If you will notice in your screenshot, the fourth occurence of location one in the table should have supply point 'B' ,but the formula given results in 'D'. It takes the Max value of the filtered table. It is not taking into account that we want the Supply[supply] with the Supply[date] closest and before the date in result[date]. The current formula just gives the max supply point before the date which could easilly be 'D' or any other higher variable instead of the most recent supply point (the one we want ) 'B'. Do you know how we could take this context into account with our DAX formula?

Column = CALCULATE(MAX(Supply[supply]),FILTER(ALL(Supply),Result[location]=Supply[location] && Result[Product]=Supply[product] 
&& Supply[date]<=Result[date]))

[VasTg · Accepted solution]

@Cary_Casey 

I overlooked the output. Try this.

Column = 
VAR SUP_DATE = CALCULATE(MAX(Supply[date]),FILTER(ALL(Supply),Result[location]=Supply[location] && Result[Product]=Supply[product] && Supply[date]<=Result[date]))
RETURN CALCULATE(MAX(Supply[supply]),FILTER(ALL(Supply),Result[location]=Supply[location] && Result[Product]=Supply[product] && Supply[date]=SUP_DATE))

Mark it as a solution if it helps.

Kudos are nice too.

[Cary_Casey]

@VasTg 

Thank you very much. This helped immensely in my learning of DAX! This will help me with several other projects to isolate the correct data.

[VasTg]

@Cary_Casey 

Glad it helped.

[Pragati11]

Hi,

One way to achieve this is create a table with column "LOCATION" having distinct values against this column. Then create a relationship between RESULT & LOCATION tables and SUPPLY and LOCATION tables.

Further you can use DAX function "RELATED" to get the calculation done.

Thanks.

[Cary_Casey]

@Pragati11 

I tried this, but it did not work as expected. The DAX RELATED functionality actually did not let me use the supply table as a related table. Thank you for your response though!