Forum Discussion

gingerbread's avatar
gingerbread
Frequent Visitor
3 years ago

Measure to dynamically calculate clusters based on report filters and condition

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:

 

CustomerActiveScore in seconds
A10,00
A00,02
A10,90
A11,00
A12,00
B10,20
B01,00
B12,30
B12,40
C01,30
C12,10
C12,50
C12,50
C13,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)
A30,00
A11,00
A12,00
B20,20
B22,30
C21,30
C22,50
C13,50

 

Your help is very much appreciated!


Thank you!

Need Help

5 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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's avatar
      gingerbread
      Frequent Visitor
      3 years ago

      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's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        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