Num(Sales, '#,##0.00')

Num, Text and number formatting in Power BI

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

Short answer: Set the measure's format in Measure tools > Format, or as a custom string such as #,##0.00. Use a dynamic format string when the format depends on context. Use FORMAT() only for titles and concatenated labels.

What it does in Qlik

Num(x [, format [, dec_sep [, thou_sep]]]) formats a number but keeps its numeric value.
Text(x) forces the text representation.
Money(x) formats as currency.
Number formats can also be set on the master measure or in the object properties.

Qlik example

Qlik chart and script

Num(Sum(Sales), '#,##0.00')
Num(Margin, '0.0%')
Num(Sum(Sales)/1000, '#,##0.0') & 'K'
Text(CustomerID) AS CustomerKey     // keep leading zeros

Power BI equivalent

Format string (keeps the number)

DAX

// Measure tools > Format > Custom: #,##0.00   or   0.0%
Sales = SUM ( Sales[Amount] )

// Dynamic format string (Format: Dynamic) - e.g. K / M suffix
SWITCH ( TRUE (),
    SELECTEDMEASURE () >= 1e6, "#,0.0,,""M""",
    SELECTEDMEASURE () >= 1e3, "#,0.0,""K""",
    "#,0" )

FORMAT for labels only

DAX

Title = "Sales: " & FORMAT ( [Sales], "#,##0" ) & " (" & FORMAT ( [Margin %], "0.0%" ) & ")"

Power Query

Power Query M

= Number.ToText([Amount], "N2", "en-US")       // text, culture-aware
= Text.PadStart(Text.From([CustomerID]), 8, "0") // keep / add leading zeros

Gotchas and behaviour differences

A measure wrapped in FORMAT() returns text. It sorts alphabetically ("10" before "9"), can't feed a chart's value axis, and makes every row non-blank, so empty rows reappear. Qlik's Num doesn't have these problems, because a dual keeps the number.
FORMAT's output depends on the report or model locale. Pass the optional locale argument, for example FORMAT([Sales], "#,##0.00", "nl-NL"), if the format has to be fixed.
DAX strings escape a quote by doubling it (""), not with a backslash. The K and M strings above use that form.
Dynamic format strings are the closest match to Qlik's expression-based number format. Visual calculations don't support them yet.

Official documentation

Num - script and chart function (Qlik Help)
Text - script and chart function (Qlik Help)
FORMAT function (DAX) (Microsoft Learn)
Create dynamic format strings for measures (Microsoft Learn)
Number.ToText (Power Query M) (Microsoft Learn)

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 Date(Date#(text, 'YYYYMMDD'), 'DD-MM-YYYY') Date#() parsing and Date() formatting Date#() interprets text as a date and Date() formats a number as a date, keeping it a dual. In Power BI, parse in Power Query with Date.FromText and set the display format on the column. DAX FORMAT() turns a date into text. 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

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.