QlikToDax

Sum(If(Status = 'Open', Amount))

Sum(If(...)) vs set analysis in Power BI

Sum(If(cond, x)) tests every row, like SUMX(T, IF(cond, x)). When the condition is a plain column comparison, a CALCULATE filter, the equivalent of set analysis, is cleaner and faster. When the condition compares two columns of the same row, keep the row-by-row form.

Direct equivalent DAX

Short answer: Comparing a column to a constant: CALCULATE(SUM(T[Amount]), T[Status] = "Open"). Comparing two columns: SUMX(T, IF(T[ShipDate] > T[DueDate], T[Amount])), or CALCULATE(SUM(T[Amount]), FILTER(T, T[ShipDate] > T[DueDate])).

What it does in Qlik

The If inside an aggregation is evaluated for each underlying record. Records where it returns NULL are skipped by Sum. It does what a set expression does, but it is calculated row by row, so it is slower on large data and can test conditions that involve several fields of the same record.

Qlik example

Qlik chart expressions
Sum(If(Status = 'Open', Amount))
Count(If(ShipDate > DueDate, OrderID))
Sum({<Status={'Open'}>} Amount)   // the set analysis version

Power BI equivalent

DAX

DAX
Open amount =
CALCULATE ( SUM ( Orders[Amount] ), Orders[Status] = "Open" )

Late orders =
COUNTROWS ( FILTER ( Orders, Orders[ShipDate] > Orders[DueDate] ) )

Late amount =
SUMX ( Orders, IF ( Orders[ShipDate] > Orders[DueDate], Orders[Amount] ) )

Gotchas and behaviour differences

  • A CALCULATE filter like Orders[Status] = "Open" replaces any slicer selection on Status, like set analysis. SUMX(IF()) respects the slicer. If the user picks "Closed", the first shows the open amount, while the second shows blank. Wrap the filter in KEEPFILTERS to get the SUMX behaviour with CALCULATE.
  • For flags used in many measures, such as "is late", add a calculated column or a Power Query column once and filter on it. It's simpler and faster.

Official documentation

Related pages

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 If(cond, then, else) If and nested If Qlik's If() maps directly to DAX IF and to Power Query's if ... then ... else. Long nested Ifs are cleaner as SWITCH(TRUE(), ...). Qlik's numeric booleans (-1/0) are the main difference. Direct equivalent Count(DISTINCT CustomerID) Count, Count(DISTINCT) and counting key fields Count(DISTINCT Field) maps to DISTINCTCOUNT. Watch out for blanks, for which table you count from, and for the Qlik habit of counting key fields. 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.