Dynamic Segmentation of callers and calls

[Cogden]

Hi there,

I was hoping someone may be able to help with a dynamic segmentation query in Power BI. 

I have an activities table (SQLActivities) which contains columns called activityID and callerID. The principle of the table is that a single callerID can have multiple activityID. I'm trying to create two pie charts to show:

1) Callers grouped into buckets based on number of activities per caller, and

2) Activities grouped by the same buckets for each caller. For the second pie chart I am essentially trying to show how many activities in each caller bucket -i.e. if two callers had 11 or more activities, how many activities were actually in that bucket.

I have created a table to specificy the values of the buckets - they are 1, 2, 3 - 5, 6 - 10, and 11 or more. I created the table as follows:

 

ActivitySegments =

Datatable(

    "Segment Key", INTEGER,

    "Bucket", String,

    "Min Activities", Integer,

    "Max Activities", Integer,

    {

        {1,"1",1,1},

        {2,"2",2,2},

        {3,"3 - 5",3,5},

        {4,"6 - 10",6,10},

        {5,"11 or more",11,99999999999}

    }

)

for the first chart I've been able to create a measure to show the number of callers which fall into each bucket using the following dax:

Activity Buckets - Callers =

VAR Summary =

    SUMMARIZE (

        FILTER(SQLActivities , SQLActivities [Filter Activities In Date] = 1),

        SQLActivities [c1_calleridid],

        "Bucket", SWITCH (

            TRUE (),

            COUNTROWS ( SQLActivities ) = 1, "1",

            COUNTROWS ( SQLActivities ) = 2, "2",

            COUNTROWS ( SQLActivities ) <= 5, "3 - 5",

            COUNTROWS ( SQLActivities ) <= 10, "6 - 10",

            COUNTROWS ( SQLActivities ) > 10, "11 or more"

        )

    )

RETURN

    SUMX ( Summary, IF ( [Bucket] = SELECTEDVALUE ( 'ActivitySegments'[Bucket] ), 1, 0 ) )

This seems to work well enough, but I cannot work out how to convert this to calculate the number of activities per caller bucket instead of the number of callers. The expected result would show the number of activities for callers per bucket.

[Anonymous · Accepted solution]

Hi @Cogden ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create measures. 

Bucket = 
var _A= COUNTROWS(FILTER(ALLSELECTED('SQLActivities'),'SQLActivities'[callerID]=MAX('SQLActivities'[callerID])))
var _bucket=SWITCH (
            TRUE (),
            _A = 1, "1",
            _A= 2, "2",
            _A <= 5, "3 - 5",
            _A <= 10, "6 - 10",
            _A > 10, "11 or more"
        )
return _bucket

Count = COUNTROWS(FILTER('SQLActivities',[Bucket] = MAX('ActivitySegments'[Bucket])))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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