Measure total is not the sum of the single rows

[KamEt69]

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!

[KamEt69 · Accepted solution]

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]

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.

[KamEt69]

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]

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.

Thank you very much for your support

[v-jianboli-msft]

Hi @KamEt69 ,

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

The measure seems works fine:

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.

[KamEt69]

Hello, Jianbo Li.

Thank you for your answer.

I posted additional details.