QlikToDax

{<Year={2023}+{2024}>} / {1-$}

Set operators: + - * / between sets and value lists in Power BI

Qlik set operators (union +, exclusion -, intersection *, symmetric difference /) work on value lists inside a modifier and on whole record sets. Value-list operations map cleanly to DAX. Record-set operations usually do not.

Partial / different approach DAX

Short answer: Value lists map to IN { }, UNION, INTERSECT and EXCEPT with TREATAS. Record-set operators such as {1-$} have no DAX equivalent; compute them as arithmetic between measures where the measure is additive.

What it does in Qlik

Set operators combine element sets ({2023}+{2024}, P(...)*P(...)) or whole sets of records ({1-$} means "records excluded by the current selection").

Qlik example

Qlik chart expressions
Sum({<Year={2023}+{2024}>} Sales)                    // either year
Sum({<Product={"*"}-{'Bike'}>} Sales)                // all products except Bike
Sum({<Product = P({<Year={2023}>} Product)
              * P({<Year={2024}>} Product)>} Sales)  // products sold in both years
Sum({1-$} Sales)                                     // what the selection excludes

Power BI equivalent

Lists of values

DAX
Sales 2023-24 = CALCULATE ( [Sales], 'Date'[Year] IN { 2023, 2024 } )

Sales not bikes =
CALCULATE ( [Sales], Product[Name] <> "Bike", NOT ISBLANK ( Product[Name] ) )

Intersection of two possible-value lists

DAX
Sales of products sold in both years =
VAR P2023 = CALCULATETABLE ( VALUES ( Sales[ProductKey] ), 'Date'[Year] = 2023 )
VAR P2024 = CALCULATETABLE ( VALUES ( Sales[ProductKey] ), 'Date'[Year] = 2024 )
RETURN
    CALCULATE ( [Sales], TREATAS ( INTERSECT ( P2023, P2024 ), Product[ProductKey] ) )

{1-$}: no direct equivalent

DAX has no "selection state" to subtract. For an additive measure like a sum, the arithmetic gives the same number:

DAX
Sales outside selection =
CALCULATE ( [Sales], REMOVEFILTERS () ) - [Sales]

// In a visual by Product: keep the row's product
Sales outside selection (by product) =
CALCULATE ( [Sales], REMOVEFILTERS (), VALUES ( Product[Name] ) ) - [Sales]

The first form is for cards and totals. In a visual it would subtract each row from the grand total, so add the row dimension back as in the second form. Neither works for non-additive measures such as distinct counts or averages. Rebuild those with an explicit EXCEPT on the key column.

Gotchas and behaviour differences

  • {"*"} in Qlik means all non-null values. A DAX <> comparison keeps blanks, hence the NOT ISBLANK.
  • Symmetric difference (/) is UNION(EXCEPT(a, b), EXCEPT(b, a)).
  • Two DAX filter arguments on different columns combine with AND. A Qlik + between two record sets is an OR. For OR across columns, filter a combined table such as FILTER(CROSSJOIN(ALL('Date'[Year]), ALL(Customer[Region])), 'Date'[Year] = 2024 || Customer[Region] = "North"). That form also ignores any slicer on Year or Region, while each Qlik modifier keeps the selection in the field it doesn't name.

Official documentation

Related pages

{<Customer=P({<Product={'Bike'}>} Customer)>} P() and E(): possible and excluded values Qlik's P() and E() functions answer questions like "sales of customers who ever bought a bike" or "customers who never did". In DAX you build the customer list with CALCULATETABLE and apply it with TREATAS or EXCEPT. Partial / different approach {<Field+={x}>} / {<Field-={x}>} / {<Field*={x}>} Set modifier operators: =, +=, -=, *= Qlik set modifiers can replace, extend, reduce or intersect the user's selection in a field. Here is how each assignment operator maps to DAX, using KEEPFILTERS, TREATAS and UNION. Partial / different approach Sum({1} Sales) / Sum({<Region=>} Sales) Ignoring selections: {1}, Field= and {1<...>} How to translate Qlik's {1} set identifier and empty field modifiers (Region=) into REMOVEFILTERS, ALL and ALLSELECTED. Includes the important difference in how chart dimensions are treated. Partial / different approach

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.