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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
ArashZ
Helper I
Helper I

Session duration per user per day

Hi,

 

I want to calculate the session duration per user per day. In the table below, we have different users with different sessions date.

 

UserNumber of sessionsSession dateSession Duration
A128/07/2021 10 AM100
B329/07/2021 11 AM130
B329/07/2021 12 PM200
B330/07/2021 13 PM250
C412/08/2021 10 AM40
C412/08/2021 11 AM30
C412/08 2021 13 PM120
C413/08/2021 14 PM140

 

I am looking for a calculated column to sum the sessions per user per day. The answer should look like this:

 

UserSum
A100
B330
B330
B250
C190
C190
C190
C140

 

You can see that for example, for user B, the first two rows are 330. It's because they happened on the same day.

Thanks.

4 REPLIES 4
VahidDM
Super User
Super User

Hi @ArashZ  Jan,

 

Try this code to add a new column:

Sum = 
CALCULATE (
    SUM ( 'Table'[Session Duration] ),
    ALLEXCEPT ( 'Table', 'Table'[User], 'Table'[Session date] )
)

 

Output:

VahidDM_0-1632521540784.png

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

Appreciate your Kudos✌️!!

Greg_Deckler
Super User
Super User

@ArashZ Try:

Column =
  VAR __User = [User]
  VAR __Date = [Session date]
RETURN
  SUMX(FILTER('Table',[User]=__User && [Session date] = __Date),[Session Duration])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerHi,

 

The solution only calculates per user, not per day. Can you fix that?

@ArashZ Ah, I missed there were different times on same day. Try:

 

Column =
  VAR __User = [User]
  VAR __Date = TRUNC([Session date])
RETURN
  SUMX(FILTER('Table',[User]=__User && TRUNC([Session date]) = __Date),[Session Duration])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.