Dynamic Grouping

Grouping in Power BI is often referred to as segmentation, chunking, binning, banding, or bucketing. For the purposes of this article, all will be referred to as the Dynamic Grouping DAX pattern.

For example, you may want to know the group totals of aged invoices in a certain time period. So, first ensure your grouping column is available in the fact table (e.g., Invoices[Aging Days]).

Aging Days =
SWITCH( TRUE(),
    Invoices[Paid in Full] = 1, 0,
    Invoices[Paid in Full] = 0, INT( TODAY() - Invoices[Invoice Date] ),
    BLANK()
)

Then, setup a disconnected Supporting Table in your data model with groups, lower limits, and upper limits:

Dynamic Grouping - Supporting Table

Then, you can use the Dynamic Grouping DAX pattern to group your invoices by the number of days they’ve aged:

Outstanding per Group =
// DAX PATTERN NAME: Dynamic Grouping
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
   [Total Amount Outstanding],
    FILTER(
        VALUES( Invoices[Aging Days] ), // replace "Invoices[Aging Days]" with the column to be grouped
            COUNTROWS(
                FILTER( 'Aging Groups', // replace "'Aging Groups'" with the supporting table name
                    AND(
                        Invoices[Aging Days] >='Aging Groups'[Min], // replace "Invoices[Aging Days]" with the column to be grouped; replace "'Aging Groups'[Min]" with the lower limit of the supporting table group
                        Invoices[Aging Days] <='Aging Groups'[Max] // replace "Invoices[Aging Days]" with the column to be grouped; replace "'Aging Groups'[Max]" with the upper limit of the supporting table group
                    )
                )
            ) > 0
    )
)

NOTE: ensure that comparisons used in the FILTER clause of the DAX are appropriate for the upper and lower limits of the groups in your supporting table (i.e., if range values are discrete, use “<=”, or if range values are duplicated, use “<” or “>” as appropriate).

NOTE: This pattern can be achieved using either the disconnected supporting table approach described above or a calculated column approach, in which case a new column could be added to the Invoices table calculating the aging group; the formula for such a calculated column could be:

Aging Group (CC) =
SWITCH( TRUE(),
   Invoices[Aging Days] = 0, "Paid", 
   Invoices[Aging Days] >= 1 && Invoices[Aging Days] <= 30, "1-30 days",        
   Invoices[Aging Days] >= 31 && Invoices[Aging Days] <= 60, "31-60 days", 
   Invoices[Aging Days] >= 61 && Invoices[Aging Days] <= 90,  "61-90 days", 
   Invoices[Aging Days] >= 91 && Invoices[Aging Days] <= 120, "91-120 days", 
   Invoices[Aging Days] >= 121 && Invoices[Aging Days] <= 180, "121-180 days", 
   Invoices[Aging Days] >= 181 && Invoices[Aging Days] <= 10000, "Over 180 days", 
   BLANK()
)

DAX Patterns - Dynamic Grouping.pbix (88.0 KB)

3 Likes

Just adding some keywords to make this pattern easier to locate via forum search:
DAX pattern, dynamic grouping, segmentation, chunking, binning, banding, bucketing, disconnected supporting table.

Related Content:

Below are a few examples of issues related to the Dynamic Grouping DAX Pattern from the eDNA resources.

Enterprise DNA Forum (Search):

https://forum.enterprisedna.co/t/dynamically-group-customers-by-ranking/3327

https://forum.enterprisedna.co/t/dynamic-grouping-via-support-tables/3326

https://forum.enterprisedna.co/t/combining-dynamic-grouping-with-additional-ranking-logic-with-dax/590

Enterprise DNA Forum (EDNA):

Enterprise DNA TV (YouTube):

Grouping & Segmenting Your Data With DAX Measure Logic - Advanced DAX

Create Custom Dynamic Groups In Power BI - Advanced DAX

How To Create Virtual Groups & New Dimensions Fast in Power BI

Segment Data Based On Percentage Groups - Advanced DAX In Power BI

Detailed Banding & Segmenting Example in Power BI using DAX

Thank so much this DAX pattern I used a lot, but I want to understand why we use COUNTROWS
it confused me.

CALCULATE( [Total Amount Outstanding], FILTER( VALUES( Invoices[Aging Days] ), // replace "Invoices[Aging Days]" with the column to be grouped COUNTROWS( FILTER( 'Aging Groups', // replace "'Aging Groups'" with the supporting table name AND( Invoices[Aging Days] >='Aging Groups'[Min], // replace "Invoices[Aging Days]" with the column to be grouped; replace "'Aging Groups'[Min]" with the lower limit of the supporting table group Invoices[Aging Days] <='Aging Groups'[Max] // replace "Invoices[Aging Days]" with the column to be grouped; replace "'Aging Groups'[Max]" with the upper limit of the supporting table group ) ) ) > 0 ) )

Hi @mahmoud.algindy. When polling the supporting table for [Aging Groups], the COUNTROWS > 0 allows one to detect when you’ve found the “matching” (correct range) row, and thus assign the correct group.
Greg

1 Like

Hi @gregorsustarsic. You are posting to a solved thread. Please create a new thread with your issue and provide all the work-in-progress files you can (e.g., PBIX, Excel or CSV, screenshots, etc.) for the forum members to use in the review of your issue.
Greg