Display First Instance of MAX Value for Card

[DAX_ConfusesMe]

Hi,

I am trying to use a measure to determine which time has the highest amount of traffic. The goal is to display the Time with the highest amount on a Card within PowerBI. The problem I run into is there are two values, both with the highest amount and I receive an error. In this case, I would want to display the first instance (in the example below, I need the return value as 10:00am).

Error: MdxScript(Model) (4, 41) Calculation error in measure 'Sheet1'[MAX Interval]: A table of multiple values was supplied where a single value was expected.

I am using this: 

Max Interval = CALCULATE(VALUES(Sheet1[Time]),FILTER(Sheet1,Sheet1[Amount]=MAX(Sheet1[Amount])))

Sample Table

Store	DOW	Time	Amount
A	Tuesday	10:00 AM	100
A	Tuesday	10:30 AM	99
A	Tuesday	11:00 AM	100
A	Tuesday	11:30 AM	87
B	Tuesday	10:00 AM	33
B	Tuesday	10:30 AM	77

[Ashish_Mathur]

Hi,

Write these measures:

Total amount = SUM(Data[Amount])

Measure = FIRSTNONBLANK(TOPN(1,VALUES(Data[Time]),[Total amount],DESC),1)

[PhilipTreacy · Accepted solution]

Hi @DAX_ConfusesMe 

Try this

Max Interval = CALCULATE(MIN(Sheet1[Time]),FILTER(Sheet1,Sheet1[Amount]=MAX(Sheet1[Amount])))

Regards

Phil

[DAX_ConfusesMe]

Ugh. I had the wrong filter. Thank you.