QlikToDax

{<Customer=P({<Product={'Bike'}>} Customer)>}

P() and E(): possible and excluded values in Power BI

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 DAX

Short answer: P() becomes a CALCULATETABLE(VALUES(Sales[CustomerKey]), <condition>) applied with TREATAS. E() becomes EXCEPT(VALUES(Customer[CustomerKey]), <that list>).

What it does in Qlik

P({set} Field) returns the possible values of a field in a set. E({set} Field) returns the excluded values. Used inside a modifier, they select a group of values (for example customers) based on a condition in another field (for example product), without filtering that other field.

Qlik example

Qlik chart expressions
// All sales (any product) of customers who bought a bike
Sum({<Customer = P({<Product={'Bike'}>} Customer)>} Sales)

// Sales of customers who never bought a bike
Sum({<Customer = E({<Product={'Bike'}>} Customer)>} Sales)

Power BI equivalent

Customers who bought (P)

Read the customer keys from the fact table. With the usual single-direction relationships, a Product filter does not reach the Customer table directly.

DAX
Sales of bike buyers =
VAR BikeBuyers =
    CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Product[Category] = "Bike" )
RETURN
    CALCULATE ( [Sales], TREATAS ( BikeBuyers, Customer[CustomerKey] ) )

Customers who never bought (E)

DAX
Sales of non-bike buyers =
VAR BikeBuyers =
    CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Product[Category] = "Bike" )
VAR Others =
    EXCEPT ( VALUES ( Customer[CustomerKey] ), BikeBuyers )
RETURN
    CALCULATE ( [Sales], TREATAS ( Others, Customer[CustomerKey] ) )

Gotchas and behaviour differences

  • The inner condition (Product[Category] = "Bike") replaces any Product filter on the page, but keeps others such as Year. That matches a $-based inner set. Wrap it in REMOVEFILTERS() if the Qlik set started with 1.
  • TREATAS replaces the existing filter on Customer[CustomerKey], just as Customer = P(...) replaces the Customer selection. Use KEEPFILTERS(TREATAS(...)) to intersect instead.
  • E() includes customers with no sales at all. VALUES(Customer[CustomerKey]) also includes them, so the two match, but their sales are blank anyway.
  • With a $-based inner set, E() also returns customers that other selections exclude, for example customers outside a selected country. In the usual $ outer set those selections still filter them out again. Inside {1<Customer = E(...)>} they don't, so build Others from ALL(Customer[CustomerKey]) to match.

Official documentation

Related pages

{<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 {<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 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

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.