Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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 |
Solved! Go to Solution.
Try this
Max Interval = CALCULATE(MIN(Sheet1[Time]),FILTER(Sheet1,Sheet1[Amount]=MAX(Sheet1[Amount])))
Regards
Phil
Proud to be a Super User!
Hi,
Write these measures:
Total amount = SUM(Data[Amount])
Measure = FIRSTNONBLANK(TOPN(1,VALUES(Data[Time]),[Total amount],DESC),1)
Try this
Max Interval = CALCULATE(MIN(Sheet1[Time]),FILTER(Sheet1,Sheet1[Amount]=MAX(Sheet1[Amount])))
Regards
Phil
Proud to be a Super User!
Ugh. I had the wrong filter. Thank you.
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |