Forum Discussion

F75's avatar
F75
Advocate I
9 years ago
Solved

CALCULATETABLE with multiple filter

I am trying to lookup data in 'input' table for a intersecation in table 'Data'.

 

One of the dimensions 'scenario' is 'input' table is available as column, but the same dimension is available in rows in 'Data' table. How do i lookup values?

 

e.g. for Row 5, loookup value in data table should be

 

2017 Plan A|  Sales |.........| 1010 

 

 

Data table

 

 

 

Input Table

 

 

 

 

Need Help
Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi F75,

     

    If there are only a few columns in Input Table, like [2017 Plan A] and [2017 Plan B], you can use below formula to create a calculate column in Data Table.

    New Unit Price =
IF (
    Data[Scenario] = "2017 Plan A",
    LOOKUPVALUE ( Input[2017 Plan A], Input[Account], Data[Account] ),
    IF (
        Data[Scenario] = "2017 Plan B",
        LOOKUPVALUE ( Input[2017 Plan B], Input[Account], Data[Account] ),
        BLANK ()
    )
)

    However, if there are many columns in Input Table in query editor, you should first unpivot it to get below format.


    Then, in modeling mode, based on above table, please create a new table.

    Table =
UNION (
    SELECTCOLUMNS (
        'Input (2)',
        "Account", 'Input (2)'[Account],
        "Plan", 'Input (2)'[Attribute],
        "Value", 'Input (2)'[Value]
    ),
    SELECTCOLUMNS (
        'Input (2)',
        "Account", 'Input (2)'[Account],
        "Plan", 'Input (2)'[Attribute.1],
        "Value", 'Input (2)'[Value.1]
    )
)

     

    Then, in Data Table, create a calculated column.

    New Unit Price 2 =
LOOKUPVALUE (
    'Table'[Value],
    'Table'[Account], Data[Account],
    'Table'[Plan], Data[Scenario]
)

    Best regards,
    Yuliana Gu

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    9 years ago

    I would recommend to unpivot your input table and then joint to you data table and the pivot it back (if required)

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi F75,

     

    If there are only a few columns in Input Table, like [2017 Plan A] and [2017 Plan B], you can use below formula to create a calculate column in Data Table.

    New Unit Price =
IF (
    Data[Scenario] = "2017 Plan A",
    LOOKUPVALUE ( Input[2017 Plan A], Input[Account], Data[Account] ),
    IF (
        Data[Scenario] = "2017 Plan B",
        LOOKUPVALUE ( Input[2017 Plan B], Input[Account], Data[Account] ),
        BLANK ()
    )
)

    However, if there are many columns in Input Table in query editor, you should first unpivot it to get below format.


    Then, in modeling mode, based on above table, please create a new table.

    Table =
UNION (
    SELECTCOLUMNS (
        'Input (2)',
        "Account", 'Input (2)'[Account],
        "Plan", 'Input (2)'[Attribute],
        "Value", 'Input (2)'[Value]
    ),
    SELECTCOLUMNS (
        'Input (2)',
        "Account", 'Input (2)'[Account],
        "Plan", 'Input (2)'[Attribute.1],
        "Value", 'Input (2)'[Value.1]
    )
)

     

    Then, in Data Table, create a calculated column.

    New Unit Price 2 =
LOOKUPVALUE (
    'Table'[Value],
    'Table'[Account], Data[Account],
    'Table'[Plan], Data[Scenario]
)

    Best regards,
    Yuliana Gu