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
Class(Age, 10)
Class (bucketing into bins)
Qlik's Class() puts a number into a fixed-width bin and returns a dual: a label such as 20<=x<30 plus the lower bound as the number. Power BI has no single function for this. Build a bin column with FLOOR (or Number.RoundDown) plus a label and a sort-by column, or use the built-in Groups and bins feature.
Partial / different approach
Fractile(Sales, 0.9)
Fractile (percentiles)
Fractile() is inclusive and interpolates between values, which is the same method as DAX PERCENTILE.INC. FractileExc() matches PERCENTILE.EXC. For a percentile over an aggregated grain (Fractile with Aggr), use PERCENTILEX.INC over a table of groups.
Direct equivalent
Median(Sales), Mode(Product), Stdev(Sales)
Median, Mode and Stdev
Median and Stdev have direct DAX functions: MEDIAN/MEDIANX and STDEV.S/STDEVX.S (Qlik's Stdev is the sample version). Mode has no DAX function. Use TOPN over a grouped table, and decide what happens on ties, which Qlik answers with NULL.
Partial / different approach
MaxString(Status)
MaxString and MinString
MaxString and MinString return the last and first text value in sort order. DAX MAX and MIN already work on text columns, so the mapping is direct. The difference is how the two tools sort text.
Direct equivalent
NullCount(Email)
NullCount, TextCount, NumericCount, MissingCount
Qlik has separate counters for NULLs, text values, numbers, and "missing" values (NULL or text). DAX columns hold one data type, so most of these turn into COUNTBLANK, COUNT or COUNTROWS with a filter. Mixed-type checks belong in Power Query.
Partial / different approach
If(Dimensionality() = 0, 'Total', ...)
Dimensionality and SecondaryDimensionality
Dimensionality() tells a measure which level of a pivot table it is on. DAX doesn't return a level number. Test each level with ISINSCOPE, deepest first, or use ISATLEVEL in a visual calculation. A pivot's column levels (SecondaryDimensionality) are handled the same way.
Partial / different approach
Sum(If(Status = 'Open', Amount))
Sum(If(...)) vs set analysis
Sum(If(cond, x)) tests every row, like SUMX(T, IF(cond, x)). When the condition is a plain column comparison, a CALCULATE filter, the equivalent of set analysis, is cleaner and faster. When the condition compares two columns of the same row, keep the row-by-row form.
Direct equivalent
Column(1) / Column(2)
Column(n) and expression labels
In a Qlik table, Column(n) or an expression label reuses the result of another measure column. In Power BI you reference the measure by name, which works anywhere. Visual calculations let you refer to the fields on the visual, which is the closest thing to "the column next to me".
Partial / different approach
Limitation: Top 10, Show others
Dimension limits and "Others"
Qlik's dimension limits show the top N values plus an optional "Others" row with the rest. Power BI's Top N visual filter covers the first part. The "Others" row needs a helper dimension table with an extra "Others" member and a measure, because Power BI can't show a member that isn't in the data.
Partial / different approach