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. ResultlocationdateResultProductone11/15/2019Y1one11/15/2019N2two11/17/2019Y1two11/17/2019Y2three12/3/2019N1three12/3/2019Y2one1/5/2020Y1one1/5/2020Y2three1/5/2020Y1three1/5/2020Y2 Supplylocationdatesupplyproductone10/11/2019A1one10/10/2019A1one10/5/2019D2one10/6/2019D2two11/15/2019C1two11/12/2019C1two11/10/2019C2three12/1/2019A1three11/30/2019A1three11/29/2019A2one12/30/2019B1one12/31/2019B1one1/1/2020B2 Desired outcome Result with supply column from supplylocationdateResultProductSupplyone11/15/2019Y1Aone11/15/2019N2Dtwo11/17/2019Y1Ctwo11/17/2019Y2Cthree12/3/2019N1Athree12/3/2019Y2Aone1/5/2020Y1Bone1/5/2020Y2Bthree1/5/2020Y1Athree1/5/2020Y2A 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.

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.

Forum Discussion

Cary_Casey

Frequent Visitor

6 years ago

Solved

Extracing Column from foreign table with conditions

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.

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.

7 Replies

VasTg
Memorable Member
6 years ago
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
- Pragati11
  Super User
  6 years ago
  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
    Frequent Visitor
    6 years ago
    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!
- Cary_Casey
  Frequent Visitor
  6 years ago
  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
    Memorable Member
    6 years ago
    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.

Forum Discussion

Extracing Column from foreign table with conditions

7 Replies

Related content

Rank with conditions

Conditional Formatting using DAX

Conditional Formatting

Conditional Formatting

conditional join