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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors