Forum Discussion

vanonyuo's avatar
vanonyuo
Frequent Visitor
6 years ago
Solved

Conditional lookup values from multiple columns in related/lookup table.

Hello,

 

I am trying to find the best way to look up values in Table 2 from multiple columns And successfull getting different errors

 

Example of what I am trying to accomplish:

If ('table1'[number] < #, Related(table2[column 1],

If ('table1'[number] >= # && 'Table1'[number] <#, Related(table2[column 2],

If ('table1'[number] >=#, Related(table2[column 3],

 

I used this formula below but did not work What is the best way to accomplish this successfully.

 

Final Column =
IF(LOOKUPVALUE('Table1'[number] < 15, 'table2'[column1],
'Table1'[number] >= 15 && 'Table1'[number] < 25, 'Table2'[column2]),
'Table1'[number] >= 25, 'Table2'[column3]))
Error: Function LOOKUPVALUE expects a column reference as argument number 1.
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, vanonyuo 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table1:

     

    Table2:

     

    There is a relationship between two tables. You may create a calculated column or a measure as below.

    Calculated column:
Column = 
IF(
    Table1[Number]>0&&Table1[Number]<4,
    RELATED(Table2[Column1]),
    IF(
        Table1[Number]>=4&&Table1[Number]<=7,
        RELATED(Table2[Column2]),
        IF(
            Table1[Number]>7,
            RELATED(Table2[Column3])
        )
    )
)

Measure:
Measure = 
var tab = 
ADDCOLUMNS(
    Table1,
    "Result",
    var _number = [Number]
    return
    IF(
        _number>0&&_number<4,
        LOOKUPVALUE(Table2[Column1],Table2[Number],_number),
        IF(
            _number>=4&&_number<=7,
            LOOKUPVALUE(Table2[Column2],Table2[Number],_number),
            IF(
                _number>7,
                LOOKUPVALUE(Table2[Column3],Table2[Number],_number)
            )
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi vanonyuo 

    I think it is a question for DAX section, not Power Query.

    And you do not need any LOOKUPVALUE.

    If you have one-to-one or many-to-one relationships you can create an easy calculated column

     

    Column = 
SWITCH(TRUE(),
'table1'[number] < 15, Related(table2[column 1]),
'table1'[number] >= 15 && 'Table1'[number] < 25, Related(table2[column 2]),
Related(table2[column 3])
)

     

    • vanonyuo's avatar
      vanonyuo
      Frequent Visitor
      6 years ago

       az38 ,

       

      Thank you so much for your repponse. I tried your recomended formula and got an error indicating to many arguments for Related, and limited to 1.

       

       

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, vanonyuo 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table1:

     

    Table2:

     

    There is a relationship between two tables. You may create a calculated column or a measure as below.

    Calculated column:
Column = 
IF(
    Table1[Number]>0&&Table1[Number]<4,
    RELATED(Table2[Column1]),
    IF(
        Table1[Number]>=4&&Table1[Number]<=7,
        RELATED(Table2[Column2]),
        IF(
            Table1[Number]>7,
            RELATED(Table2[Column3])
        )
    )
)

Measure:
Measure = 
var tab = 
ADDCOLUMNS(
    Table1,
    "Result",
    var _number = [Number]
    return
    IF(
        _number>0&&_number<4,
        LOOKUPVALUE(Table2[Column1],Table2[Number],_number),
        IF(
            _number>=4&&_number<=7,
            LOOKUPVALUE(Table2[Column2],Table2[Number],_number),
            IF(
                _number>7,
                LOOKUPVALUE(Table2[Column3],Table2[Number],_number)
            )
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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