Aggregation & chart functions
Aggr, TOTAL, Count distinct, Only, Concat, FirstSortedValue, Rank and other chart-level functions translated to DAX.
Sum(TOTAL Sales) / Sum(TOTAL <Category> Sales)
The TOTAL qualifier
Qlik's TOTAL qualifier makes an aggregation ignore the chart dimensions but keep the selections. In DAX that is ALLSELECTED. TOTAL <Field> keeps one dimension, which maps to removing only the other dimension columns.
Direct equivalent
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
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
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
Concat(DISTINCT Product, ', ')
Concat (string aggregation)
Concat() joins values into one string. DAX has CONCATENATEX and Power Query has Text.Combine. The sort order and the duplicate handling are different from Qlik's defaults.
Direct equivalent
FirstSortedValue(Product, -Aggr(Sum(Sales), Product))
FirstSortedValue
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
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
GetFieldSelections(Region)
GetFieldSelections and GetSelectedCount
GetFieldSelections returns the selected values of a field as text, often for dynamic titles. In DAX you combine ISFILTERED, VALUES and CONCATENATEX.
Partial / different approach