Lookup value by comparing two columns from two tables and show 3rd column as output

[labuser1235]

I would like to compare two columns from two different tables and get the price value in the calculated column. 

Let the two tables be Fruit Name Type and Price columns as Table 1 along with Date Fruit Name Type and Need price to look up from table 1 and add it in table 2. Relations between the tables are Furit Name (Active Relation)and Type. 

Can we achieve this by DAX as well ??? 

Fruit Name	Type	Price
Orange	Type-1	5
Orange	Type-2	7
Orange	Type-3	10
Apple	Type-1	3
Apple	Type-2	5
Apple	Type-3	8
Grapes	Type-1	2
Grapes	Type-2	4
Grapes	Type-3	6

Date	Fruit Name	Type	Lookvalue for price
1/31/2020	Orange	Type-2
1/31/2020	Apple	Type-3
1/31/2020	Grapes	Type-1
1/31/2020	Orange	Type-1
1/31/2020	Orange	Type-3
1/30/2020	Apple	Type-1
1/30/2020	Grapes	Type-2
1/30/2020	Grapes	Type-3
1/30/2020	Oranges	Type-1
1/30/2020	Oranges	Type-1

Please help... I have tried multiple ways may be I was doing it worng....please help me with the DAX formula or Calulated column formula to get the result. 

[labuser1235]

If I add another column which tells only the price of respective years then how should I match it with repective to years. 

Please help with these @amitchandak @TomMartens 

Fruit Name	Type	2020 - Price	2019 - Price
Orange	Type-1	5	4
Orange	Type-2	7	6
Orange	Type-3	10	8
Apple	Type-1	3	2
Apple	Type-2	5	4
Apple	Type-3	8	7
Grapes	Type-1	2	2
Grapes	Type-2	4	3
Grapes	Type-3	6	6

Date	Fruit Name	Type	Lookvalue for price
1/31/2020	Orange	Type-2
1/31/2020	Apple	Type-3
1/31/2020	Grapes	Type-1
1/31/2020	Orange	Type-1
1/31/2020	Orange	Type-3
1/30/2020	Apple	Type-1
1/30/2020	Grapes	Type-2
1/30/2020	Grapes	Type-3
1/30/2020	Oranges	Type-1
1/30/2020	Oranges	Type-1
12/30/2019	Apple	Type-3
12/30/2019	Grapes	Type-1
12/30/2019	Orange	Type-1
12/30/2019	Orange	Type-3
12/31/2019	Apple	Type-1
12/31/2019	Grapes	Type-2
12/31/2019	Grapes	Type-3
12/31/2019	Oranges	Type-1
12/31/2019	Orange	Type-2
12/31/2019	Apple	Type-3

[amitchandak]

Earlier will not allow any function inside. So create year using dates

Year1 = year(Date)

now you can add clause like

Year1 = earlier(Year1) // This year

Year1 = earlier(Year1)-1 // This vs Last

[labuser1235]

@amitchandak  I have understood that adding year column would bring only the year. However, I am only confused because the price in table1 tells only for that year which is in column and the year in table2 is present in Rows. How should I match and get the exact price. Please help me I am really consfused.  

[amitchandak]

Can you share the raw data structure again with output structure? The one you shared last not making clear what is input and what is output

[labuser1235]

@amitchandak Please check the raw data again. I am expecting output as in Table2 last column which I have added.

Input Table1

Fruit Name	Type	2020 - Price	2019 - Price
Orange	Type-1	5	4
Orange	Type-2	7	6
Orange	Type-3	10	8
Apple	Type-1	3	2
Apple	Type-2	5	4
Apple	Type-3	8	7
Grapes	Type-1	2	2
Grapes	Type-2	4	3
Grapes	Type-3	6	6

Expected Output in Table2: Last Column -- Output- Lookup Price (Column Name)

Date	Year	Fruit Name	Type	Output - Lookup Price
1/31/2020	2020	Orange	Type-2	7
1/31/2020	2020	Apple	Type-3	8
1/31/2020	2020	Grapes	Type-1	2
1/31/2020	2020	Orange	Type-1	5
1/31/2020	2020	Orange	Type-3	10
1/30/2020	2020	Apple	Type-1	3
1/30/2020	2020	Grapes	Type-2	4
1/30/2020	2020	Grapes	Type-3	6
1/30/2020	2020	Oranges	Type-1	5
1/30/2020	2020	Oranges	Type-1	5
12/30/2019	2019	Apple	Type-3	7
12/30/2019	2019	Grapes	Type-1	2
12/30/2019	2019	Orange	Type-1	4
12/30/2019	2019	Orange	Type-3	8
12/31/2019	2019	Apple	Type-1	2
12/31/2019	2019	Grapes	Type-2	3
12/31/2019	2019	Grapes	Type-3	6
12/31/2019	2019	Oranges	Type-1	4
12/31/2019	2019	Orange	Type-2	6
12/31/2019	2019	Apple	Type-3	7

[amitchandak]

Check -https://www.dropbox.com/s/d4pmpfein2h3nuv/differentyearprice.pbix?dl=0

Not sure how you want to get different dates when you have data at the yearly level.

You can rename and change datatype in new table

[labuser1235]

@amitchandak You're a Genuis. Just the kind of data I was looking for.

PFB the screenshots for reference

[labuser1235]

I understood that I have to add another column deriving year from the date. 

My doubt is how will I lookup with the below DAX expression in this case as the column name gives only the price of that specific year in Table1 and the year is in rows in Table2. Please brief on this @amitchandak 

new column in table two = maxx(filter(table1, table1[type]=table2[type] && table1[Fruit Name]=table2[Fruit Name]),table1[price])

Table1

Fruit Name	Type	2020 - Price	2019 - Price
Orange	Type-1	5	4
Orange	Type-2	7	6
Orange	Type-3	10	8
Apple	Type-1	3	2
Apple	Type-2	5	4
Apple	Type-3	8	7
Grapes	Type-1	2	2
Grapes	Type-2	4	3
Grapes	Type-3	6	6

Table2

Date	Year	Fruit Name	Type	Lookvalue for price
1/31/2020	2020	Orange	Type-2
1/31/2020	2020	Apple	Type-3
1/31/2020	2020	Grapes	Type-1
1/31/2020	2020	Orange	Type-1
1/31/2020	2020	Orange	Type-3
1/30/2020	2020	Apple	Type-1
1/30/2020	2020	Grapes	Type-2
1/30/2020	2020	Grapes	Type-3
1/30/2020	2020	Oranges	Type-1
1/30/2020	2020	Oranges	Type-1
12/30/2019	2019	Apple	Type-3
12/30/2019	2019	Grapes	Type-1
12/30/2019	2019	Orange	Type-1
12/30/2019	2019	Orange	Type-3
12/31/2019	2019	Apple	Type-1
12/31/2019	2019	Grapes	Type-2
12/31/2019	2019	Grapes	Type-3
12/31/2019	2019	Oranges	Type-1
12/31/2019	2019	Orange	Type-2
12/31/2019	2019	Apple	Type-3

[amitchandak · Accepted solution]

Try

new column in table two = maxx(filter(table1, table1[type]=table2[type] && table1[Fruit Name]=table2[Fruit Name]),table1[price])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[EllenG]

Dear @amitchandak, this is a brilliantl!!!   Could you help me modify slightly, to add a search function to the second column? Specifically 

> The first columns have exact matches

> The second columns, I need to search  to see whether Table 1 term is within Table 2 column

What would you suggest? 

[labuser1235]

This worked like charm Thanks alot @amitchandak . 

[TomMartens]

Hey @labuser1235 ,

you can use the DAX formula LOOKUPVALUE inside a calculated column, in my example, I call the table that contains the price column sourcetable and the other table targetable. The statement will look like this:

price =
lookupvalue(
'<sourcetable>'[Price],
'<sourcetable>'[Fruit Name], 'targettable>'[FruitName],
'<sourcetable>'[Type], 'targettable>'[Type]
)

Hopefully, this provides what you are looking for.

Regards,

Tom

[datadog_ab]

Short and sweet!

[labuser1235]

It throws "A table of multiple values was supplied where a single value was expected error", when i actually work on larger records of data. 

My data contains 21K records. Please help @TomMartens