Lookup Max Value from related table

[Auski]

Hi again,

I have had so much success with solutions I thought I would try my luck again. As I am learning PBI, I am still working through even the basic of functions sometimes!

I want to create a new column 'in Results Table' that looks up 'questionid' in table named 'Evaluatons Forms' and return the highest value in the 'answervalue' column.

Essentially : 

Result = LOOKUPVALUE('Evaluation Forms'[answervalue],'Evaluation Forms'[questionid],'Results Table'[questionid])

but with MAX result

Example:

I am unable to share my Pbix, but happy to provide more details if required.
I'm almost 100% sure my request is more than on the basic side

Thanks!

Evaluations Forms Table

questionid	answervalue
00f14e48-1b44-4673-b076-d622f1ec58d9	0
00f14e48-1b44-4673-b076-d622f1ec58d9	2
00f14e48-1b44-4673-b076-d622f1ec58d9	1
1c62c71e-933e-4d6f-897d-c6a1edee307a	0
1c62c71e-933e-4d6f-897d-c6a1edee307a	1
1c62c71e-933e-4d6f-897d-c6a1edee307a	2
5c9ece52-a2b8-4397-81f0-f710e6ef60f4	1
5c9ece52-a2b8-4397-81f0-f710e6ef60f4	2
5c9ece52-a2b8-4397-81f0-f710e6ef60f4	0
6ad65fbb-ba91-42dc-8ed3-3d45451f1712	1
6ad65fbb-ba91-42dc-8ed3-3d45451f1712	0
6ad65fbb-ba91-42dc-8ed3-3d45451f1712	2
a828ba94-006f-4ac2-9a8f-701fb607d828	1
a828ba94-006f-4ac2-9a8f-701fb607d828	2
a828ba94-006f-4ac2-9a8f-701fb607d828	0
a8a8946c-3447-454e-99bf-4c1eb10b3a2e	2
a8a8946c-3447-454e-99bf-4c1eb10b3a2e	1
a8a8946c-3447-454e-99bf-4c1eb10b3a2e	0
c7737470-6a3d-4c57-817c-324dcb22ffa4	0
c7737470-6a3d-4c57-817c-324dcb22ffa4	2
c7737470-6a3d-4c57-817c-324dcb22ffa4	1
ec040969-1625-4201-be8e-e0e2756d50d7	2
ec040969-1625-4201-be8e-e0e2756d50d7	1
ec040969-1625-4201-be8e-e0e2756d50d7	0

Results Table

questionid	New Column Result
6ad65fbb-ba91-42dc-8ed3-3d45451f1712	2
1c62c71e-933e-4d6f-897d-c6a1edee307a	2
c7737470-6a3d-4c57-817c-324dcb22ffa4	2
ec040969-1625-4201-be8e-e0e2756d50d7	2
5c9ece52-a2b8-4397-81f0-f710e6ef60f4	2
a8a8946c-3447-454e-99bf-4c1eb10b3a2e	2
00f14e48-1b44-4673-b076-d622f1ec58d9	2
a828ba94-006f-4ac2-9a8f-701fb607d828	2

[Auski]

To be more clear... I have two tables

Table 1

Table 2

I would like the MAX value from 'Table 1'[answervalue] to be the returned value in 'Table 2'[Max answervalue].

Hope that makes sense,

Thanks for your time

[Ahmedx]

pls try

[Auski]

Hi Ahmed,

Thanks for your time with this. I have modified your pbix to better show what I am after...

I would like a new column in Table 2 to lookup max 'answervalue' in Table 1

*Seems I don't even know how to attach a pbix 😓

[Ahmedx · Accepted solution]

pls try

[Auski]

Fantastic, that did the trick. Thank you @Ahmedx