Measure to dynamically calculate clusters based on report filters and condition

[gingerbread]

Hi,

I have a table "Data" and it consists of the following columns: [Customer], [Active] and [Score in seconds]. Here is an example of the values:

Customer	Active	Score in seconds
A	1	0,00
A	0	0,02
A	1	0,90
A	1	1,00
A	1	2,00
B	1	0,20
B	0	1,00
B	1	2,30
B	1	2,40
C	0	1,30
C	1	2,10
C	1	2,50
C	1	2,50
C	1	3,50

I would like to create a measure in DAX, which will cluster customer values on the following conditions:

• Group customers by name
• Group scores, with less then 1 second apart, whithin customer group
• I would like to be able to see how many clusters per customer I have and be able to flexibly filter on the Active = 0 or Active =1. My original tabel has many other filers, I will need for analysis.

So the end result should have a measure which would count number of clusters, per customer. The [Range (Min Score)] measure is a nice to have result:

Customer	Total Clusters	Range (Min Score)
A	3	0,00
A	1	1,00
A	1	2,00
B	2	0,20
B	2	2,30
C	2	1,30
C	2	2,50
C	1	3,50

Your help is very much appreciated!

Thank you!

[v-yanjiang-msft]

Hi @gingerbread ,

According to your description, here's my solution.

Create two measures.

Cluster =
VAR Result =
    ADDCOLUMNS (
        ALLSELECTED ( 'Data' ),
        "Cluster",
            RANKX (
                FILTER (
                    ALLSELECTED ( 'Data' ),
                    [Customer] = EARLIER ( [Customer] )
                        && ABS ( 'Data'[Score in seconds] - EARLIER ( 'Data'[Score in seconds] ) ) >= 1
                ),
                'Data'[Score in seconds],
                ,
                ASC,
                DENSE
            )
    )
VAR ClusteredData =
    GROUPBY (
        Result,
        [Customer],
        [Cluster],
        "Range(Min Score)", MINX ( CURRENTGROUP (), 'Data'[Score in seconds] )
    )
RETURN
    IF (
        CONTAINS ( ClusteredData, [Range(Min Score)], MAX ( 'Data'[Score in seconds] ) )
            && MAX ( 'Data'[Customer] )
                = MAXX (
                    FILTER ( ClusteredData, [Range(Min Score)] = MAX ( 'Data'[Score in seconds] ) ),
                    [Customer]
                ),
        1,
        0
    )

Total Clusters =
VAR _Nest =
    MINX (
        FILTER (
            ALLEXCEPT ( 'Data', 'Data'[Active] ),
            [Cluster] = 1
                && 'Data'[Customer] = MAX ( 'Data'[Customer] )
                && 'Data'[Score in seconds] > MAX ( 'Data'[Score in seconds] )
        ),
        'Data'[Score in seconds]
    )
RETURN
    IF (
        ISFILTERED ( Data[Active] ),
        COUNTROWS (
            FILTER (
                ALL ( 'Data' ),
                'Data'[Active] = SELECTEDVALUE ( Data[Active] )
                    && 'Data'[Customer] = MAX ( 'Data'[Customer] )
                    && 'Data'[Score in seconds] >= MAX ( 'Data'[Score in seconds] )
                    && 'Data'[Score in seconds]
                        < IF (
                            _Nest <> BLANK (),
                            _Nest,
                            MAXX ( ALL ( 'Data' ), 'Data'[Score in seconds] ) + 1
                        )
            )
        ),
        COUNTROWS (
            FILTER (
                ALL ( 'Data' ),
                'Data'[Customer] = MAX ( 'Data'[Customer] )
                    && 'Data'[Score in seconds] >= MAX ( 'Data'[Score in seconds] )
                    && 'Data'[Score in seconds]
                        < IF (
                            _Nest <> BLANK (),
                            _Nest,
                            MAXX ( ALL ( 'Data' ), 'Data'[Score in seconds] ) + 1
                        )
            )
        )
    )

Put Customer, Score in seconds, Total Clusters measure in visual, and Cluster in visual filter and select "Show item when the value is 1", get the correct result. It also works for the Active slicer.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gingerbread]

Hey @v-yanjiang-msft thank you very much for the quick answer. I have populated the data set with more values and new data. It seems to work very well for the first cluster whithin the group, but not the rest of the groups. See picture bellow eg:

Do you know what could be the issue and what needs to change?

[v-yanjiang-msft]

Hi @gingerbread ,

Can't see the reason from the snapshot, it seems you use the original pbix I send to you, if the data is not sensitive, could you please send it back here, then I can dig into it.

Best regards,

Community Support Team_yanjiang

[gingerbread]

sure 🙂 , here is the file (link valid for 7 days): https://we.tl/t-M74Zj7MhN2

[amitchandak]

@gingerbread , I think you are looking for

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ