Grouping by a column, using measure as value

[JonSwed]

Hi all,

I need some help.

My data looks like this:

I created the session2 column via a measure which counts the number of visitIdUnique_.

I want to get sessions2 per fullVisitorId (users) and then present sessions per user as a histogram. i.e. show the number of all users with 1 session, the number with 2 etc etc. Ideally, I want to create an additional column which is labelled buckets e.g. 1 session, 2 - 5 sessions, 6 - 10 sessions etc which I can then use in a chart.

Can anyone suggest a DAX formula/table combination I could use to do this? Many thanks for any help!

[v-alq-msft · Accepted solution]

Hi, @JonSwed 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

You may create a measure as below.

CountSessions = 
var tab = 
SUMMARIZE(
    ALL('Table'),
    'Table'[FullVisitorId],
    "Count",
    DISTINCTCOUNT('Table'[VisitIdUnique])
)
var newtab = 
ADDCOLUMNS(
    Test,
    "Result",
    SWITCH(
        [Range],
        "1 session",
        COUNTROWS(
            FILTER(
                tab,
                [Count]=1
            )
        ),
        "2-5 sessions",
        COUNTROWS(
            FILTER(
                tab,
                [Count]>=2&&
                [Count]<=5
            )
        ),
        "6-10 sessions",
        COUNTROWS(
            FILTER(
                tab,
                [Count]>=6&&
                [Count]<=10
            )
        )
    )
)
return
SUMX(
    newtab,
    [Result]
)

Result:

Best Regards

Allan

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

[amitchandak]

@JonSwed , if you want to create a column on a measure that is not possible. The workaround is independent table and segmentation/binning

https://www.youtube.com/watch?v=CuczXPj0N-k

or refer : https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[JonSwed]

Hi again.

So, I'm getting somewhere but not quite there yet.

I used the dynamic segmention code that you linked to and is also described here.

This is allowing date selection but I'm struggling to get the metrics I want. Right now it's summing the number of sessions in each segment, whereas I want the count of fullVisitorId.

Here's my code:

VAR CustomersMaxPerYear = 
ADDCOLUMNS(
    VALUES ( GA_Hits_Mod_Help_UserTest[visitorIdTest] ),
    "MaxSales",
    CALCULATE(
        SUMX(  
            ALLSELECTED ( GA_Hits_Mod_Help_UserTest[hitDate_] ),
            CALCULATE( DISTINCTCOUNT(GA_Hits_Mod_Help_UserTest[visitIdUnique_]) )
        )
    )
)
VAR MinRange = MIN ( Segment[Min] )
VAR MaxRange = MAX ( Segment[Max] )
VAR CustomersInRange = 
FILTER (
    CustomersMaxPerYear,
    [MaxSales] > MinRange &&
    [MaxSales] <= MaxRange
)
RETURN
    CALCULATE ( DISTINCTCOUNT(GA_Hits_Mod_Help_UserTest[visitIdUnique_] ), CustomersInRange )

This returns the sum of sessions (visitIdUnique) in each segment, rather than the number of users in each session segment (users are visitorIdTest).
Here's an example of how the data looks:

And this is what my output is currently looking like:

Any pointers gratefully received. Thanks!

[JonSwed]

Thanks for that. Would it not be possible to create a new table where I group the fullVisitorId's by the sum of the sessions, based off this table?