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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.