QlikToDax

Match(Region, 'North', 'South')

Match, MixMatch and WildMatch in Power BI

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 DAX Power Query

Short answer: For "is it in the list", use IN { } in DAX or List.Contains in M. For the index, use SWITCH or List.PositionOf(...) + 1. DAX comparisons ignore case, so Match (case-sensitive) needs EXACT to match exactly.

What it does in Qlik

  • Match(s, e1, e2...) returns the position of the first expression equal to s, or 0. It is case-sensitive.
  • MixMatch is the same but case-insensitive.
  • WildMatch is case-insensitive and supports * and ? wildcards.

Qlik example

Qlik script
If(Match(Region, 'North', 'South'), 'Core', 'Other') AS RegionGroup
MixMatch(Status, 'open', 'closed') AS StatusIndex
If(WildMatch(Product, 'Bike*', '*helmet*'), 1, 0) AS IsBikeGear

Power BI equivalent

DAX (case-insensitive by nature)

DAX
Region group =
IF ( Customer[Region] IN { "North", "South" }, "Core", "Other" )

Status index =
SWITCH ( Orders[Status], "open", 1, "closed", 2, 0 )

Is bike gear =
IF (
    LEFT ( Product[Name], 4 ) = "Bike" || CONTAINSSTRING ( Product[Name], "helmet" ),
    1, 0
)

Power Query (case-sensitive by default)

Power Query M
// Match (case-sensitive): position, 0 when not found
= Table.AddColumn(Source, "RegionIndex", each List.PositionOf({"North", "South"}, [Region]) + 1, Int64.Type)

// MixMatch (case-insensitive)
= Table.AddColumn(Source, "StatusIndex", each
    List.PositionOf({"open", "closed"}, [Status], Occurrence.First, Comparer.OrdinalIgnoreCase) + 1, Int64.Type)

// WildMatch-style prefix and contains tests
= Table.AddColumn(Source, "IsBikeGear", each
    if Text.StartsWith([Product], "Bike", Comparer.OrdinalIgnoreCase)
       or Text.Contains([Product], "helmet", Comparer.OrdinalIgnoreCase) then 1 else 0, Int64.Type)

Gotchas and behaviour differences

  • Case sensitivity is reversed. Power Query compares text case-sensitively by default, like Match. DAX =, IN and SWITCH ignore case, like MixMatch. Use EXACT() in DAX when case matters.
  • List.PositionOf returns -1 when the value is not found, hence the + 1 to get Qlik's 0.
  • DAX SEARCH supports * and ? but finds the pattern anywhere in the text. It is not anchored like WildMatch, and it errors when there is no match unless you give the fourth argument.
  • The model engine is case-insensitive. "North" and "NORTH" in a column are stored as one value (whichever arrives first), even though Power Query treated them as different.

Official documentation

Related pages

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

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.