Need help grouping visitorId by session count and date

[JonSwed]

Hi all,

So I'm importing Google Analytics data and have created a new table (from another table) where I'm counting sessions by user.
This is the DAX I used to create my table:

Table = SUMMARIZE( ALL ('GA_pageviews'[hitDate_], 'GA_pageviews_Help'[fullVisitorId], 'GA_pageviews'[country_], 'GA_pageviews'[visitIdUnique_] ),

'GA_pageviews_Help'[hitDate_],

'GA_pageviews_Help'[fullVisitorId],

GA_pageviews_Help[country_],

"Count", DISTINCTCOUNT( ('GA_Hits_Mod_Pageviews_Help'[visitIdUnique_]) )

)

My table then  looks like this.

What I want to do is create a bucketed table based on this data. So I can group users by the number of sessions they have made in a given time range. Buckets like 1 - 5, 6 - 10 etc. Essentially, a histogram where the x axis will be bucketed number of sessions and the y axis will be a count of users who have made that many sessions. Time range can be changed by the user based on a slicer on the dashboard.

Many thanks for any help you can offer. 

[JonSwed]

Hi all. Again, many thanks for the help to date.

The dax code, as a measure works perfectly. When I create a table including this measure it labels the users, and their sessions, with the correct grouping. However, as a calculated column it does not work. This is my next challenge as I need to visualise this data as a histogram, rather than presenting it in a table.

The calculated column I guess is not working correctly because it is not operating on the aggregated fullvisitorIds which the measure is. As the data can be sliced by the user, and includes a filter for another dimension (country) I need this to have the dynamic capability that a measure has.

Many thanks in advance for any further help.

[JonSwed]

Thanks for the input guys. Unfortunately the dax solution does not seem to work. I'll be looking into it in more detail and checking out the video which was shared.

[Pragati11]

Hi @JonSwed ,

Doesn't seem to understand why you say that DAX is not working?

Please add details on why it's not working.

Thanks,

Pragati

[Pragati11 · Accepted solution]

Hi @JonSwed ,

I don't think you need a new table to create groupings in SESSION column. You can create a new column and use it for visualisation in your report.

sessionGrouping = IF([session] >= 1 && [session] <= 5, "1-5 Group",

                                   IF([session] >= 6 && [session] <= 10, "6-10 Group", "Above 10 Group"))

See the above example for column creation.

Thanks,

Pragati

[JonSwed]

I spoke too soon - this seems to be working. Many thanks.

[amitchandak]

@JonSwed , You can do bucket using segmentation or measure or column.

For measure refer my video and file attached after signature

https://youtu.be/CuczXPj0N-k

Or refer these

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