How to do Dynamic Group Counting in Power BI


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.

RicoZhou_0-1666662804547.png

 

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

RicoZhou_1-1666662804551.png

 

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

RicoZhou_2-1666662804553.png

 

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

RicoZhou_3-1666662804561.png

 

RicoZhou_4-1666662804562.png

 

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

RicoZhou_5-1666662804563.png

 

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

RicoZhou_6-1666662804569.png

 

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.

RicoZhou_7-1666662804580.png

 

Related links: 

Solved: Dynamic grouping Power BI – Microsoft Power BI Community

 

 

Author: Xiaojie Tang

Reviewer: Kerry Wang & Ula Huang



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*