Conditional, string & null logic
If, Match, WildMatch, Pick, Alt, Dual and null handling, string functions (SubField, TextBetween, KeepChar, Num, Trim, Replace…), and the case-sensitivity and BLANK differences that trip people up.
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
Match(Region, 'North', 'South')
Match, MixMatch and WildMatch
Match, MixMatch and WildMatch test a value against a list and return its position (or 0). They differ in case sensitivity and wildcard support, and so do DAX and Power Query, in the opposite direction from what you might expect.
Partial / different approach
Pick(n, expr1, expr2, ...)
Pick (and Pick(Match()) measure switching)
Pick(n, ...) returns the n-th expression. It maps to SWITCH. The common Qlik measure-selector idiom Pick(Match(vMeasure, ...)) is better rebuilt with Power BI field parameters.
Direct equivalent
Null(), IsNull(), NullAsValue
NULL handling vs DAX BLANK and M null
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
Dual('Jan', 1)
Dual values
Qlik fields can hold a dual value: a text and a number at the same time. Power BI columns have one data type, so you use a text column sorted by a number column (Sort by column).
Partial / different approach
Alt(Sales, 0)
Alt and Coalesce
Qlik Coalesce() and DAX COALESCE() both return the first argument that isn't NULL or BLANK. Alt() is different: it returns the first argument that has a valid number representation, so Alt('abc', 0) returns 0. DAX has no single function that skips text that way. Power Query uses the ?? operator.
Partial / different approach
Round(Sales, 0.01)
Round, Floor and Ceil
Qlik's Round(x, step, offset) rounds to a multiple of a step such as 0.01 or 5, not to a number of decimal places. DAX ROUND takes digits and MROUND takes a multiple. Watch the tie-breaking: Qlik rounds halves up. DAX ROUND rounds away from zero. Power Query Number.Round uses banker's rounding (round half to even) by default.
Partial / different approach
SubField(FullName, ' ', 1)
SubField
SubField has two modes. With an index (SubField(s, ',', 2)) it returns one part of the string. Without an index, in a LOAD, it creates one row per part. In Power Query, use Text.Split for the first and "Split column by delimiter into rows" for the second. DAX has no split function, but PATHITEM with SUBSTITUTE returns the nth part.
Partial / different approach
TextBetween(Code, '[', ']')
TextBetween
Power Query's Text.BetweenDelimiters is a direct match, and the Extract > Text Between Delimiters menu generates it. Two differences: the occurrence index is zero-based, and Qlik returns NULL when a delimiter is missing. Add a guard if you depend on that NULL. In DAX, combine MID and SEARCH.
Direct equivalent
KeepChar(Phone, '0123456789')
KeepChar and PurgeChar
In Power Query, Text.Select keeps only the listed characters (KeepChar) and Text.Remove drops them (PurgeChar). Both accept ranges such as {"0".."9"}. DAX has no equivalent. You would need one nested SUBSTITUTE per character, so clean the text in Power Query.
Partial / different approach
Num(Sales, '#,##0.00')
Num, Text and number formatting
In Qlik, Num(x, '#,##0.00') returns a dual: the number stays sortable and summable and only the display changes. DAX FORMAT() returns text, which breaks sorting, aggregation and chart axes. The right translation is usually a measure or column format string, not a function. Use FORMAT only for labels.
Partial / different approach
Capitalize(CustomerName)
Upper, Lower and Capitalize
UPPER and LOWER exist in DAX and Power Query (Text.Upper, Text.Lower). Capitalize, which is proper case, only exists in Power Query as Text.Proper. DAX has no proper-case function. The bigger trap is case sensitivity: the Power BI model compares text case-insensitively, while Power Query is case-sensitive.
Partial / different approach
Mid(Code, Index(Code, '-') + 1)
Left, Right, Mid, Index and Len
DAX has the same 1-based functions: LEFT, RIGHT, MID, LEN, plus FIND or SEARCH in place of Index. In Power Query the functions are zero-based. Text.PositionOf returns -1 when nothing is found, where Qlik's Index returns 0, and Text.Middle starts at offset 0.
Direct equivalent
Trim(Replace(Name, ' ', ' '))
Replace and Trim
Qlik Replace maps to DAX SUBSTITUTE, not DAX REPLACE, which is positional. Trim has a subtle difference. Qlik Trim and Power Query Text.Trim only remove leading and trailing spaces. DAX TRIM also collapses repeated spaces inside the text to one, so the results can differ.
Partial / different approach
If(ProductName like '*bike*', 'Bikes')
The like operator and string comparisons
Qlik's like matches against a pattern with * (any characters) and ? (one character). DAX's closest equivalents are CONTAINSSTRING, which is case-insensitive and supports * and ?, and SEARCH. Power Query has no wildcard operator. Use Text.Contains, Text.StartsWith and Text.EndsWith, with a comparer to ignore case.
Partial / different approach