QlikToDax

FirstSortedValue(Product, -Aggr(Sum(Sales), Product))

FirstSortedValue in Power BI

FirstSortedValue returns the value of one field on the record with the lowest (or, with a minus sign, highest) sort weight, such as the top-selling product or the latest status. In DAX, use TOPN plus a tie check, or LASTNONBLANKVALUE for "latest".

Partial / different approach DAX

Short answer: Use TOPN(1, VALUES(Product[Name]), [Sales], DESC) and return a value only when exactly one row comes back. Qlik returns NULL on ties, but TOPN returns all tied rows.

What it does in Qlik

FirstSortedValue(value, sort_weight [, rank]) sorts the records by sort_weight (ascending; use - for descending) and returns value from the first one, or from the n-th with rank. If several records share the winning weight, it returns NULL.

Qlik example

Qlik chart expressions
FirstSortedValue(Product, -Aggr(Sum(Sales), Product))   // best-selling product
FirstSortedValue(Status, -StatusDate)                   // latest status
FirstSortedValue(DISTINCT Product, -UnitPrice)          // most expensive product

Power BI equivalent

Best-selling product (NULL on ties, like Qlik)

DAX
Top product =
VAR t = TOPN ( 1, VALUES ( Product[Name] ), [Sales], DESC )
RETURN
    IF ( COUNTROWS ( t ) = 1, MAXX ( t, Product[Name] ) )

Latest value by date

DAX
Latest status =
LASTNONBLANKVALUE ( 'Date'[Date], MAX ( StatusLog[Status] ) )

This returns the status on the last date that has one. If there are two statuses on that date, MAX picks one alphabetically; Qlik would return NULL.

Gotchas and behaviour differences

  • TOPN returns all tied rows, so the tie check above copies Qlik's NULL. Drop the check and use CONCATENATEX(t, ...) if you would rather show every tied value.
  • Qlik's rank argument (2nd, 3rd ...) maps to INDEX(2, ...), or to TOPN combined with RANKX.
  • Products with blank sales sort last in DESC order with TOPN. Check what you want when nothing sold.

Official documentation

Related pages

Avg(Aggr(Sum(Sales), Customer)) Aggr (nested aggregation) Aggr builds a virtual table of aggregated values, for example sales per customer, so you can aggregate again. In DAX that is an iterator (AVERAGEX, MAXX, COUNTROWS(FILTER(...))) over VALUES or SUMMARIZE that calls a measure. Partial / different approach Rank(Sum(Sales)) Rank Qlik's Rank() ranks rows within the current column segment and has its own tie modes and a text format ("3 - 4"). In DAX, use RANKX or the newer RANK window function. Pick the ties option deliberately. Partial / different approach Only(Region) Only Only() returns a value when exactly one value is possible and null otherwise. In DAX that is SELECTEDVALUE, which also accepts an alternate result. Direct equivalent Peek(field_name [, row_no [, table_name]]) Peek (running balances in the script) Peek reads a field from a row already loaded into the output table. It is used for running balances and for reading values into variables. Power Query has no row-by-row state, so use grouped list logic, a SQL window function upstream, or a DAX measure. 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.