CALCULATETABLE with multiple filter

[F75]

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

[v-yulgu-msft · Accepted solution]

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

[F75]

Thanks @v-yulgu-msft and @parry2k

[parry2k]

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