QlikToDax

If(Dimensionality() = 0, 'Total', ...)

Dimensionality and SecondaryDimensionality in Power BI

Dimensionality() tells a measure which level of a pivot table it is on. DAX doesn't return a level number. Test each level with ISINSCOPE, deepest first, or use ISATLEVEL in a visual calculation. A pivot's column levels (SecondaryDimensionality) are handled the same way.

Partial / different approach DAX Visual calculations

Short answer: Replace Dimensionality() = 0 with NOT ISINSCOPE(lowest level column) && NOT ISINSCOPE(higher level column), and so on. Write it as SWITCH(TRUE(), ISINSCOPE(Product[Product]), ..., ISINSCOPE(Product[Category]), ..., grand total).

What it does in Qlik

Dimensionality() returns the number of dimensions that apply to the current row: 0 on the grand total, 1 on a first-level subtotal, and so on. SecondaryDimensionality() does the same for column dimensions in a pivot table. They are mostly used to show different logic, or nothing, on total rows.

Qlik example

Qlik chart expression
// Pivot: Region > Country > City
If(Dimensionality() = 3, Sum(Sales),
   If(Dimensionality() = 0, Null(), Avg(Aggr(Sum(Sales), City))))

Power BI equivalent

DAX measure with ISINSCOPE

DAX
Sales by level =
SWITCH (
    TRUE (),
    ISINSCOPE ( Geo[City] ), [Sales],                              // Dimensionality() = 3
    ISINSCOPE ( Geo[Region] ), AVERAGEX ( VALUES ( Geo[City] ), [Sales] ),  // 1 or 2
    BLANK ()                                                       // grand total
)

Test the deepest level first. ISINSCOPE(Geo[Region]) is also true on the city rows, because the region is still grouped there.

Visual calculation

DAX
Hide total = IF ( ISATLEVEL ( [City] ), [Sales] )

ISATLEVEL only exists in visual calculations. It refers to fields on the visual, not model columns.

Gotchas and behaviour differences

  • HASONEVALUE isn't the same as ISINSCOPE. A total can have one value when only one city is selected, so HASONEVALUE treats it as a detail row. ISINSCOPE checks grouping, which is what Dimensionality does.
  • Qlik counts levels by position, so the same expression works with any dimension. DAX names columns, so a field-parameter visual needs one ISINSCOPE test per possible column.

Official documentation

Related pages

Sum(TOTAL Sales) / Sum(TOTAL <Category> Sales) The TOTAL qualifier Qlik's TOTAL qualifier makes an aggregation ignore the chart dimensions but keep the selections. In DAX that is ALLSELECTED. TOTAL <Field> keeps one dimension, which maps to removing only the other dimension columns. Direct equivalent Avg(Aggr(Sum(Sales), Customer)) Aggr (nested aggregation) Aggr builds a virtual table of aggregated values, for example sales per customer, so you can aggregate again. In DAX that is an iterator (AVERAGEX, MAXX, COUNTROWS(FILTER(...))) over VALUES or SUMMARIZE that calls a measure. Partial / different approach Cyclic master dimension, or a variable-driven dimension/measure Cyclic dimensions and dynamic measures A Qlik cyclic dimension lets the user cycle a chart through several fields. Power BI field parameters do the same: a slicer switches the field (or measure) a visual uses. Direct equivalent

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.