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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KamEt69
Frequent Visitor

Measure total is not the sum of the single rows

Hello!

 

I have created the following measure:

 

''LoginTime =
SUMX (
    D_TblDate,
    DATEDIFF (
        [FirstLogin],
        [LastLogActivity],
        SECOND
    )
)
 
 
This measure show in seconds the time a user was logged in.
When I show it in a table with the users on the rows it works perfeclty, however on the total does not shows the sum of the login time of the users.
I know that this behavior is technically correct but it not what is required of the measuere.
 
I saw that some solutions may require the use of SUMMARIZE or HASONEVALUE, but those solutions seemed a bit to complicated and I was wandering if there was a simpler way.
 
Thank you for your help!
1 ACCEPTED SOLUTION
KamEt69
Frequent Visitor

Here is the solution:

 

''LoginTime =
SUMX (
    D_TblUsers,
    SUMX (
        D_TblDate,
        DATEDIFF (
            [FirstLogin],
            [LastLogActivity],
            SECOND
        )
    )
)
 
 
In this whay the SUMX will loop not only through the date table but also through the user table, therefore giving the right total.

View solution in original post

6 REPLIES 6
KamEt69
Frequent Visitor

Here is the solution:

 

''LoginTime =
SUMX (
    D_TblUsers,
    SUMX (
        D_TblDate,
        DATEDIFF (
            [FirstLogin],
            [LastLogActivity],
            SECOND
        )
    )
)
 
 
In this whay the SUMX will loop not only through the date table but also through the user table, therefore giving the right total.
v-jianboli-msft
Community Support
Community Support

Hi @KamEt69 ,

 

Pleae give more details about the two measures' DAX.

Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

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

The pbix is huge and it would take hours to anonimize data.

you can immagine a table with 2 columns:

  • userid
  • eventtime

then I calculate a column that is event date by taking just the date part from event time:

Event_Date = DATEVALUE(Fog[event_time])
the column event date is used to link the date table.
 
the 2 metrics to calculate min and max activity are litterally:
LastLogActivity = MAX(Flog[event_time])
FirstLogin = MIN(Flog[event_time])

then I calculate the the logintime as follows:
 
''LoginTime =
SUMX (
D_TblDate,
DATEDIFF (
[FirstLogin],
[LastLogActivity],
SECOND
)
)
 
but this calculate the difference between the first and the last logintime of the selected day
instead of summing the sum of the login times of all the users.
KamEt69
Frequent Visitor

Hello, thank you for your answer.

first login and last login activity are not column but measueres created on a single column event time as follows:

 

LastLogActivity = MAX(Flog[event_time])
FirstLogin = MIN(Flog[event_time])

 

Here how I see the data for a particular day for a series of user.

 

KamEt69_0-1666255145278.png

 

Thank you very much for your support

 

 

v-jianboli-msft
Community Support
Community Support

Hi @KamEt69 ,

 

Based on your description, I have creaetd a simple sample:

vjianbolimsft_0-1666242880633.png

The measure seems works fine:

vjianbolimsft_1-1666242926767.png

 

Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

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

Hello, Jianbo Li.

Thank you for your answer.

I posted additional details.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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