cancel
Showing results 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

Helper I

## How to summarize a grouped date with the highest value

This is a very new experience to me and after a few attempts I am out of ideas on how to achieve this.

Essentially, I need to show the highest condition for the day, however our data is being captured hourly, so I need to group the timestamps by date. That first part I managed to do easily. The second bit is where I'm stuck.

Above is a screenshot of a scenario. Ignore the Manual and Current non compliant columns as the condition column already calculates the final values I need to summarise. So for this instance, the whole day should be flagged as 1, how can I make a new column that will look at the highest condition valueof the day and return it, this means the new column should only have the value 1 across all rows. Thanks in advance

1 ACCEPTED SOLUTION
Super User

Hi, considering that you would need a column, I created this sample dataset and created a conditional column:

Below is the column calculation:

MaxCond =
CALCULATE(
MAX(DateCond[Condition]),
ALLEXCEPT(
DateCond,
DateCond[orderdate]
)
)
As you can see 05/03/2024 has max condition 1, 06/03 has 0 and 07/03 has 2

If this resolves your problem, then please accept this as solution to help others. Thanks!
4 REPLIES 4
Super User

Hi,

Write this calculated column formula

Highest condition value = calculate(max(Data[Condition]),filter(Data,Data[Date]=earlier(Data[Date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

It didn't work, I'm getting the highest value possible in all rows, which is 2

Super User

Hi, considering that you would need a column, I created this sample dataset and created a conditional column:

Below is the column calculation:

MaxCond =
CALCULATE(
MAX(DateCond[Condition]),
ALLEXCEPT(
DateCond,
DateCond[orderdate]
)
)
As you can see 05/03/2024 has max condition 1, 06/03 has 0 and 07/03 has 2

If this resolves your problem, then please accept this as solution to help others. Thanks!
Helper I

It worked, I had to edit the formula to also filter by location. I got a follow up question, how can I aggregate that into one single value for count? Off your example, there would be a count for each condition. Though it counts only one instance. So there would be

0 = 1 day

1 = 1 day

2 = 1 day