LOOKUP

In a visual calculation, an expression's value at the row some columns' values name, the others kept from the current row.

A visual calculation works on the table behind a visual: a row for each group on the visual's axes and one for each of their totals. To try one in the OAX view, give a table that shape with DEFINE TABLE ... WITH VISUAL SHAPE: its axes, their groups, the TRUE/FALSE column that marks each group's totals and the order, and with DENSIFY a name for the column that marks rows the shape fills in. Then write the calculation as a COLUMN of that table, as Power BI's own queries do. The examples below are queries of that kind, small enough to check by hand.

Group: Window and visual calculations. Compatible with DAX.

Syntax

LOOKUP ( <expression>, <column>, <value> [, <column>, <value>] … [, <mode>] )

Returns

Same type as the expression. The expression's value at the row the values name.

Remarks

• The fields not named keep the current row's values: on IPA's 2026 cell, LOOKUP ( [Liters], [Year], 2025 ) is IPA's 2025, on the totals row the 2025 total.
• When no row of the visual has the values, the result is BLANK.
• When the values leave more than one row possible, it is an error. With years and quarters on the rows, LOOKUP ( [Liters], [Quarter], 1 ) fails on the grand total, which has no year to keep: name the year too.
• LOOKUPWITHTOTALS takes the fields not named at their totals instead.
• It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.

Compared with DAX

DAX's INFERRED also clears the filters of the fields the visual's shape ties to the ones named, such as a month's sort-by column. OpenBI reads INFERRED as EXPLICIT: on every shape tried (a month with its sort-by number, products under their category, two axes) Power BI Desktop answered the same both ways, so no case is known to differ.

Examples

The same product's 2025, and Pils in the same year

Products on the rows, years on the columns, with their totals.

DEFINE
    TABLE Visual =
        DATATABLE (
            "Product", STRING, "Year", INTEGER, "Liters", INTEGER, "All products", BOOLEAN, "All years", BOOLEAN,
            {
                { "IPA", 2025, 60, FALSE, FALSE }, { "IPA", 2026, 80, FALSE, FALSE }, { "IPA", BLANK (), 140, FALSE, TRUE },
                { "Pils", 2025, 100, FALSE, FALSE }, { "Pils", 2026, 120, FALSE, FALSE }, { "Pils", BLANK (), 220, FALSE, TRUE },
                { BLANK (), 2025, 160, TRUE, FALSE }, { BLANK (), 2026, 200, TRUE, FALSE }, { BLANK (), BLANK (), 360, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS GROUP [Product] TOTAL [All products] ORDER BY [Product]
            AXIS COLUMNS GROUP [Year] TOTAL [All years] ORDER BY [Year]
            DENSIFY "Added"
    COLUMN Visual[In 2025] = LOOKUP ( [Liters], [Year], 2025 )
    COLUMN Visual[Pils] = LOOKUP ( [Liters], [Product], "Pils" )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Product", [Product], "Year", [Year], "Liters", [Liters],
    "In 2025", [In 2025],
    "Pils", [Pils]
)
ORDER BY ISBLANK ( [Product] ), [Product], ISBLANK ( [Year] ), [Year]

Two values, and a year the visual lacks

DEFINE
    TABLE Visual =
        DATATABLE (
            "Product", STRING, "Year", INTEGER, "Liters", INTEGER, "All products", BOOLEAN, "All years", BOOLEAN,
            {
                { "IPA", 2025, 60, FALSE, FALSE }, { "IPA", 2026, 80, FALSE, FALSE }, { "IPA", BLANK (), 140, FALSE, TRUE },
                { "Pils", 2025, 100, FALSE, FALSE }, { "Pils", 2026, 120, FALSE, FALSE }, { "Pils", BLANK (), 220, FALSE, TRUE },
                { BLANK (), 2025, 160, TRUE, FALSE }, { BLANK (), 2026, 200, TRUE, FALSE }, { BLANK (), BLANK (), 360, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS GROUP [Product] TOTAL [All products] ORDER BY [Product]
            AXIS COLUMNS GROUP [Year] TOTAL [All years] ORDER BY [Year]
            DENSIFY "Added"
    COLUMN Visual[Pils in 2026] = LOOKUP ( [Liters], [Product], "Pils", [Year], 2026 )
    COLUMN Visual[In 2027] = LOOKUP ( [Liters], [Year], 2027 )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Product", [Product], "Year", [Year], "Liters", [Liters],
    "Pils in 2026", [Pils in 2026],
    "In 2027", [In 2027]
)
ORDER BY ISBLANK ( [Product] ), [Product], ISBLANK ( [Year] ), [Year]

Related functions

LOOKUPWITHTOTALS, COLLAPSEALL, PREVIOUS, LOOKUPVALUE.

Last updated 3 November 2026 · OpenBI Studio 0.3.74