alternative to lookupvalue & related??

[unknown917]

Hello,

I have 2 tables in which I have a many to 1 relationship between IDs.  I need to look up the ID (text) from table 1 and return a measure value (number) in table 2.  The measure is calculated from what if parameters.  I understand that measures cannot be used in LOOKUPVALUE.  RELATED does not also recognize measures.

Below is an example of my dataset.  

Table 1
ID
1
2
3
1
4
3
2
6
7
5
5
8

Table 2
ID	Value (measure)
1	15
2	23
3	78
4	54
5	2
6	36
7	14
8	5

My expected output is:

Table 1
ID	Output
1	15
2	23
3	78
1	15
4	54
3	78
2	23
6	36
7	14
5	2
5	2
8	5

Do I have any options, or do I need to rethink my approach?  The what if parameters are an attempt to allow the end user to enter manual data to update the report.

(SWITCH?, maxx(filter(table1,table1(ID)=table2(ID)),table2(Value))???

Any help would be greatly appreciated!

[v-nuoc-msft]

Hi @unknown917 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-nuoc-msft]

Hi @unknown917 

Thank you very much lbendlin for your prompt reply.

Here is a simple example of my design for your problem, try the following:

Here's some dummy data

Value (Measure) = 
var _whatifparameter = SELECTEDVALUE('WhatIfParameter'[WhatIfParameter])
RETURN
IF(
    ISFILTERED('WhatIfParameter'[WhatIfParameter]),
    SELECTEDVALUE('Table 2'[Value]) * _whatifparameter,
    SELECTEDVALUE('Table 2'[Value])
)

Create a measure.

Output = 
MAXX(
    FILTER(
        'Table 2', 
        'Table 2'[ID] = SELECTEDVALUE('Table 1'[ID])
    ),
    'Table 2'[Value (Measure)]
)

Here is the result.

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

If you already have a relationship then let it do the work for you.

What is the measure computing?

[unknown917]

The measure is calculating from other measure in the table.  There are several what if parameters that need to have manual touch