Class (bucketing into bins) in Power BI

What it does in Qlik

Class(expression, interval [, label [, offset]]) returns a dual value. The text is a<=x<b, and the number is the lower bound a. label replaces the x in the text, and offset moves the starting point of the bins. For example, Class(23, 10) returns 20<=x<30, and Class(23, 10, 'x', 5) returns 15<=x<25.

Qlik example

// script
Class(Age, 10, 'age') AS AgeBand

// chart (calculated dimension)
=Class(Aggr(Sum(Sales), Customer), 1000)

Power BI equivalent

Calculated columns (DAX)

Age band start = INT ( Customer[Age] / 10 ) * 10    -- INT rounds down, also for negatives

Age band =
VAR lo = INT ( Customer[Age] / 10 ) * 10
RETURN FORMAT ( lo, "0" ) & "<=age<" & FORMAT ( lo + 10, "0" )

Select Age band and use Sort by column > Age band start. Otherwise "100<=age<110" sorts before "20<=age<30".

Power Query

= Table.AddColumn(Source, "AgeBandStart",
    each Number.RoundDown([Age] / 10) * 10, Int64.Type)

Groups and bins (no code)

In Power BI Desktop, right-click a numeric or date column and choose New group, then set Group type to Bin and pick a bin size. This creates a bin column whose value is the lower bound. It covers the plain Class(x, n) case.

Bucketing a measure, the Class(Aggr()) case

// Disconnected table 'Sales band' with columns [Start] and [End]
Customers in band =
VAR lo = SELECTEDVALUE ( 'Sales band'[Start] )
VAR hi = SELECTEDVALUE ( 'Sales band'[End] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Customer[CustomerID] ),
            VAR s = [Sales]
            RETURN NOT ISBLANK ( s ) && s >= lo && s < hi   -- Aggr skips customers with no sales
        )
    )

Without the NOT ISBLANK test, customers with no sales in the current selection would count in the band that contains 0. Calculated columns cannot react to slicers. A dynamic Class(Aggr(...)) dimension needs a disconnected band table plus a measure like this one, sometimes called dynamic segmentation.

Related pages