How to do Dynamic Group Counting in Power BI

[v-rzhou-msft]

In this article, we would like to calculate the difference between each product's production date and the corresponding product's event date, and then count the differences in groups dynamically according to the value selected in the slicer.

Sample

In the sample data, there are two tables.

Table 1 is the production date table of the product, including product name and production date.

Table 2 shows the event occurrence date of each product, including product name and event date.

Then we calculate the difference in days between the date of each event and the production date. They are dynamically grouped according to the value selected in slicer.

Here are 2 solutions.

Solution 1

Create an auxiliary visual on the left to dynamically display the values selected in the three slicers, thus forming 4 different ranges.

The visual on the right counts the number of days according to the range selected by the slicers.

Step1

Create the measure

A DaysPerComplain =
VAR _Pd =
    CALCULATE (
        MAX ( 'production date list'[production date] ),
        'production date list'[Product] = MIN ( 'Table'[Product] )
    )
RETURN
    DATEDIFF ( _Pd, MIN ( 'Table'[event date] ), DAY )

Result

In this measure, variable _pd returns the production date of the current product, then use DATEDIFF() function to calculate the difference in days between production date and event date.

Step2

Go to Modeling - New parameter and add 3 slicers

Step3

Create 3 measures to return the values selected in slicers,

Slicer1 Value = SELECTEDVALUE('Slicer1'[Slicer1])

Slicer2 Value = SELECTEDVALUE('Slicer2'[Slicer2])

Slicer3 Value = SELECTEDVALUE('Slicer3'[Slicer3])

Step4

Create a table named Stage

And create the measures below

A Range =

VAR _v1 = [Slicer1 Value]

VAR _v2 = [Slicer2 Value]

VAR _v3 = [Slicer3 Value]

VAR _cur =

    MIN ( Stage[Column1] )

RETURN

    SWITCH (

        TRUE (),

        _cur = "Stage 1",

            "0-" & _v1 & " days",

        _cur = "Stage 2",

            _v1 & "-" & _v2 & " days",

        _cur = "Stage 3",

            _v2 & "-" & _v3 & " days",

        _cur = "Stage 4",

            ">" & _v3 & " days"

    )

A Count =
VAR _cur =
    MIN ( Stage[Column1] )
VAR _PName = "Product A"
RETURN
    SWITCH (
        TRUE (),
        _cur = "Stage 1",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Product] = _PName
                        && [A DaysPerComplain] <= [Slicer1 Value]
                )
            ),
        _cur = "Stage 2",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Product] = _PName
                        && [A DaysPerComplain] > [Slicer1 Value]
                        && [A DaysPerComplain] <= [Slicer2 Value]
                )
            ),
        _cur = "Stage 3",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Product] = _PName
                        && [A DaysPerComplain] > [Slicer2 Value]
                        && [A DaysPerComplain] <= [Slicer3 Value]
                )
            ),
        _cur = "Stage 4",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Product] = _PName
                        && [A DaysPerComplain] > [Slicer3 Value]
                )
            )
    )

Result

Solution 2

Without creating an auxiliary visual on the left, you can add a tooltip to the visual on the right. Display its range in the tooltip.

Related links:  

Solved: Dynamic grouping Power BI - Microsoft Power BI Community

Author: Xiaojie Tang

Reviewer: Kerry Wang & Ula Huang