Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NabihaF
Frequent Visitor

Calculating cumulative number of sessions per unique user.

This is what my data looks like:

NabihaF_0-1656421918546.png

 

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.

 
4 REPLIES 4
NabihaF
Frequent Visitor

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

NabihaF_0-1656427517331.png

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

Unique User CountTotal Sessions
117
112

And also this data across any selected time period:

NabihaF_2-1656427583796.png

@tamerj1 @amitchandak  , would really appreciate your support.

 

HI @NabihaF,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello,

 

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

I can see aggregated data for all users:

NabihaF_0-1656658275642.png

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:

NabihaF_1-1656658371268.png

 

HI @NabihaF,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors