QlikToDax

Null(), IsNull(), NullAsValue

NULL handling vs DAX BLANK and M null in Power BI

Qlik NULL, DAX BLANK and Power Query null follow different arithmetic and comparison rules. This is one of the most common reasons migrated numbers do not reconcile.

Partial / different approach DAX Power Query

Short answer: In Qlik, NULL + 1 is NULL. In DAX, BLANK() + 1 is 1 and BLANK() = 0 is TRUE (use == for a strict test). In Power Query, null + 1 is null. Replace NullAsValue with a "Replace values" step.

What it does in Qlik

In Qlik, arithmetic with NULL returns NULL. A & NULL returns A. A <> NULL is true, and A = NULL, A < NULL and A > NULL are false. Null values cannot be selected and are not counted. NullAsValue turns nulls into a selectable value at load time.

Qlik example

Qlik script
NullAsValue Region;
Set NullValue = '<missing>';
LOAD CustomerID, Region, If(IsNull(Discount), 0, Discount) AS Discount
FROM [lib://Data/customers.qvd] (qvd);

Power BI equivalent

Power Query (where Qlik scripts did it)

Power Query M
= Table.ReplaceValue(Source, null, "<missing>", Replacer.ReplaceValue, {"Region"})
= Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Discount"})

DAX rules to remember

DAX
-- BLANK() + 1        = 1         (Qlik: NULL)
-- BLANK() = 0        = TRUE      (use BLANK() == 0 for FALSE)
-- BLANK() = ""       = TRUE
-- 1 / BLANK()        = Infinity  (use DIVIDE)
Discount or zero = COALESCE ( SUM ( Sales[Discount] ), 0 )

Gotchas and behaviour differences

  • An IF(Sales[Discount] = 0, ...) test is also true for blank discounts. Use ISBLANK or == to keep them apart.
  • COALESCE([Measure], 0) makes visuals show every dimension member, because rows are no longer blank. Use it sparingly.
  • If fact rows have keys that are missing from the dimension, Power BI adds a "(Blank)" member to the dimension. Qlik shows those records as null in the dimension field.
  • DAX does not keep NULL and empty string apart in comparisons (BLANK() = "" is TRUE). Power Query does: null and "" are different values.

Official documentation

Related pages

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 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 ApplyMap('MapName', expr [, default]) Mapping Load and ApplyMap ApplyMap looks up a value in a two-column mapping table during load. In Power BI, use a merge in Power Query, a record lookup, or simply a relationship with RELATED. The behaviour differs for case sensitivity, duplicates and missing keys. 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.