topic Re: Calculating cumulative number of sessions per unique user. in DAX Commands and Tips

Calculating cumulative number of sessions per unique user.

Anonymous — Tue, 28 Jun 2022 13:13:45 GMT

This is what my data looks like:

I have unique user IDs, and the number of sessions for that ID on a given date.

Now i want to create a basic line chart which can show the cumulative number of sessions per unique user.

And a distribution of all the unique users as per the number of sessions they have had.

Please help.

Re: Calculating cumulative number of sessions per unique user.

Anonymous — Wed, 29 Jun 2022 00:15:25 GMT

I would like to explain this query a bit further. For example, this is my sample data:

Now out of this data, i need to see the following:

Unique User Count	Total Sessions
1	17
1	12

And also this data across any selected time period:

tamerj1 amitchandak , would really appreciate your support.

Re: Calculating cumulative number of sessions per unique user.

Anonymous — Fri, 01 Jul 2022 01:08:46 GMT

HI Anonymous,

In fact, I think you can use the default visual aggregate feature to achieve your requirement.
You can create a table visual with the date(switch to hierarchy mode and keep year, and month levels), add the 'user id' field with aggregate mode 'distinct count', and add the session field with 'count' mode.

Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng

Re: Calculating cumulative number of sessions per unique user.

Anonymous — Fri, 01 Jul 2022 06:53:01 GMT

Hello,

Thank you for your response. but this does not really give me what i need.

I can see aggregated data for all users:

I have managed to 'Group by' user ID, to get what i want, but i have to remove the date column, otherwise, i dont get the sum of sessions by user ID. I still need to add the date somehow.

This is what i have so far:

Re: Calculating cumulative number of sessions per unique user.

Anonymous — Wed, 27 Jul 2022 08:08:25 GMT

HI Anonymous,

Did you mean to get the calculated result that aggregated based on particular field groups without adding them to the current table visual?
If that is the case, you can try to create a variable with summarize function to group current records with specific fields. After these, you can use the iterator function to aggregate these summarized results.

Measure Totals, The Final Word

formula1 = VAR summary = SUMMARIZE ( ALLSELECTED ( Table ), [Year], [Month], [User id], "Session Count", COUNT ( Table[Session] ) ) RETURN SUMX ( summary, [Session Count] )

For the second visual effect, you need a new parameter table with number fields to use as the axis. Then you can use the following measure formula to show the results based on the 'user count' that calculates by date ranges:

formula2 = VAR currNumber = MAX ( NewTable[Number] ) VAR summary = GROUPBY ( SUMMARIZE ( Table, [Year], [Month], [User id], "Session Count", COUNT ( Table[Session] ) ), [Session Count], "User Count", COUNTX ( CURRENTGROUP (), [User id] ) ) RETURN MAXX ( FILTER ( summary, [User Count] = currNumber ), [Session Count] )

Regards,

Xiaoxin Sheng