Daily DAX : Day 444 LOOKUPWITHTOTALS

Power BI DAX Function: LOOKUPWITHTOTALS

LOOKUPWITHTOTALS is a DAX function introduced in Power BI as part of visual calculations (available since around May 2025). It is designed specifically for use in visual calculations on matrix or table visuals.

Syntax

LOOKUPWITHTOTALS(
    <expression>,
    <column_reference>, <value>
    [, <column_reference>, <value>] ...
)
  • <expression>: The measure or expression to evaluate (e.g., a sales amount measure).
  • <column_reference>: A column on the visual's axes (rows or columns).
  • <value>: The specific value to match in that column.
  • Additional pairs of column_reference and value are optional for multiple filters.

How It Works

LOOKUPWITHTOTALS looks up and evaluates the expression in the visual's matrix where the specified columns match the given values. For any columns on the visual not specified in the function, it automatically uses the grand total (i.e., ignores filters on those columns).

This differs from the related LOOKUP function, which respects the current row/column context for unspecified columns.

Use Cases

  • Retrieving a higher-level total (e.g., yearly total) while showing details (e.g., quarterly breakdown).
  • Comparing detailed values against a subtotal or grand total without complex measures using CALCULATE and REMOVEFILTERS.
  • Creating contributions to parent totals or simple variances in matrix visuals.

Example

Imagine a matrix visual with Fiscal Year and Quarter on rows, and a measure [Sales Amount].

  • LOOKUP([Sales Amount], [Fiscal Year], "FY2018")
    → Returns sales for the current quarter in FY2018 (respects quarter filter).
  • LOOKUPWITHTOTALS([Sales Amount], [Fiscal Year], "FY2018")
    → Returns the total sales for FY2018 (ignores quarter filter, uses grand total across quarters).

This allows you to add a visual calculation column showing "FY2018 Total" repeated on every quarter row for easy comparison.

Key Benefit

It simplifies writing calculations that reference higher-level aggregates directly on the visual, reducing the need for complex traditional DAX measures.

Note: This function is intended for visual calculations only and works within the context of the visual's matrix.

Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more

Daily DAX : Day 447 INFO.VIEW.COLUMNS

Power BI DAX Function: INFO.VIEW.COLUMNS() INFO.VIEW.COLUMNS() is a DAX table function that returns a table containing metadata about all columns in the current Power BI semantic model. Description This function provides detailed information on each column, including: Table : The name of the table the column belongs to. Name : The column name. Description : Any description added to the column. Expression : The DAX formula if it's a calculated column (blank for regular columns). DataCategory : Category like "Time", "Image", etc. DataType : The data type of the column. IsHidden : Whether the column is hidden. And other metadata fields. It uses friendly names (e.g., table and column names instead of IDs) and is designed for ease of use compared to the base INFO.COLUMNS() function. Syntax INFO.VIEW.COLUMNS() No parameters. Returns a table. Restrictions Requires write permissions on the semantic model. ...
Read more