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  
1AAA12334
2BBB23434
3CCCxxx
4DDD45652
5EEExxx



table2

IDName  
1Kim
2John
3Alec
4Phill
5Noah

 

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:

IDName  Dimension  
1KimAAA
2JohnBBB
3Alec 
4PhillDDD
5Noah 

 

How can I do this?

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.

3 Replies

  • 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.