Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to do a LOOKUPVALUE with FILTER?

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

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
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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  
    1AAA12334
    1AAA12334
    1BBBxxx
    1AAA12334
    1CCCxxx


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


    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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.