Set Analysis
Qlik set expressions and how to express the same filter logic with CALCULATE, filter arguments and table functions in DAX.
Sum({<Field={value}>} Sales)
Set Analysis basics
How a basic Qlik set expression such as Sum({<Year={2024}>} Sales) maps to CALCULATE with filter arguments in DAX, and where the two engines behave differently.
Direct equivalent
{<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
{<Product={"Bike*"}>} / {<Customer={"=Sum(Sales)>10000"}>}
Searches in set modifiers (wildcards, >, aggregated conditions)
How Qlik searches inside set modifiers (numeric ranges, wildcards and expression searches such as "=Sum(Sales)>10000") translate to DAX filters, and why the expression search needs ALLSELECTED to match.
Partial / different approach
{<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
{<Year={2023}+{2024}>} / {1-$}
Set operators: + - * / between sets and value lists
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
Sum({<Year={$(=Max(Year))}>} Sales)
Dollar-sign expansion in sets: $(=Max(Year))
Dollar-sign expansions such as $(=Max(Year)) put a calculated value into a set expression. In DAX you use a variable, but it is evaluated per cell, not once per chart.
Partial / different approach