How to do a LOOKUPVALUE with FILTER?

[Anonymous]

Hello,

I have two tables that follow this structure:
table1

ID	Dimension	Validation
1	AAA	12334
2	BBB	23434
3	CCC	xxx
4	DDD	45652
5	EEE	xxx

table2

ID	Name
1	Kim
2	John
3	Alec
4	Phill
5	Noah

The tables are joined by the ID column. I want to use a LOOKUPVALUE on table2 to search the value from the Dimension column on table1, but I want this LOOKUPVALUE to be done only on rows where the column Validation <> "xxx" (is different from "xxx").

My expected result would be like this:

ID	Name	Dimension
1	Kim	AAA
2	John	BBB
3	Alec
4	Phill	DDD
5	Noah

How can I do this?

[selimovd]

Hey @Anonymous ,

if you want that as a measure the code like this would work:

Dimension Value = CALCULATE( MAX( table1[Dimension  ] ), table1[Validation] <> "xxx" )

If you want that as a calculated column the following code should work:

Dimension Value Column = IF( RELATED( table1[Validation] ) <> "xxx", RELATED( table1[Dimension] ) )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hello, @selimovd  thanks for the response!

I want to create a calculated column, but when I try to use this code that you created, the RELATED function does not give me any option to be searched in the table1, as if I had not even linked. But when I make this same calculated column in table1, the RELATED function brings me all the values ​​in table2.

The link for my table1 and table2 are like:

table1   * ----------1  table2

but the Dimension column of table1 always shows only two possible values for the same ID: the actual value that I want to get and the value "xxx".
For example: for the column in table1 with ID=1 

ID	Dimension	Validation
1	AAA	12334
1	AAA	12334
1	BBB	xxx
1	AAA	12334
1	CCC	xxx

so, the RELATED function in table2 should bring me the Dimension = AAA

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following formula:

Dimension = 
CALCULATE(
    MAX(Table1[Dimension]),
    FILTER(
        Table1,
        Table1[Validation] <> "xxx"
        &&Table1[ID]=Table2[ID]
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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