Sum events that occur at thesame date and time

[Anonymous]

Hi All,

I have a datetime column and a measure that sums the max values grouped by TimeStart. Below is my measure:

The below measure works but with some weird issues.

compute_sum_of_max_duration_per_start_time = SUMX(
SUMMARIZE(ExecutionLog3,
ExecutionLog3[TimeStart],
"Max Durations", MAX(ExecutionLog3[ReportProcessDuration])),[Max Durations])

I am experiencing a weird issue where for thesame date and time, the max value is not taken.

As you can see in the table below, I was expecting that at 4:39:17, the result would be 81. Instead for that time, I have two records. Why is my grouping not working properly in this case and what can I do to solve it?

[v-xicai]

Hi @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may only display [TimeStart] and [compute_sum_of_max_duration_per_start_time] in the table visual.

According to the unique key principle, it will return one row record for the same key filed. Once you display other different field except the key field (for example the [[TimeStart]]), the measure will return related values in more than one record.

Or you may create calculated column instead of measure using formula above.

Best Regards,

Amy

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

[kentyler]

This might help. From a post on sqlbi.com

One of the latest addition to DAX has been the ability to apply an external filter context to SUMMARIZECOLUMNS, which makes this function a perfect replacement for SUMMARIZE in DAX. In The Definitive Guide to DAX I introduced such a function, but at the time of writing it was used onl...SUMMARIZECOLUMNS you can write:

SUMMARIZECOLUMNS (
    'Date'[Calendar Year], 
    'Product'[Color], 
    "Sales Amount", SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
)

instead of:

SUMMARIZE (
    Sales,
    'Date'[Calendar Year], 
    'Product'[Color], 
    "Sales Amount", SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
)

I know there are some issues using Summarize with complex filter conditions that do not affect summarize columns.

Another way of approaching it would be to summarize your data before loading it into power bi, in sql or excel.