Error in selecting the highest values

Question

hi , could help where I am wrong. I need to select the max value . but all the values gets selected. Where should I correct it?

3-6PM highest = calculate(MAX(measurement[sum_wc_consumption]), filter(measurement, measurement[Parsed_Date]>= Today() -7 && measurement[Parsed_Date]<=Today()-6 ),measurement[Intervals]="3PM-6PM")

johnt75 · Answer

I think that means that there are multiple entries which share the same value for sum_wc_consumption.You'll need to use some logic which makes sense for your business case to choose one, but you can add additional criteria into the TOPN function based on different columns. For example, you could choose the earliest occurence with something likeTOPN ( 1, measurement, measurement[sum_wc_consumption], DESC, measurement[Parsed_Date], ASC, measurement[Time], ASC ),

johnt75 · Answer

Try3-6PM highest =
CALCULATE (
    MAX ( measurement[sum_wc_consumption] ),
    REMOVEFILTERS ( measurement ),
    measurement[Parsed_Date]
        &gt;= TODAY () - 7
        &amp;&amp; measurement[Parsed_Date]
            &lt;= TODAY () - 6,
    measurement[Intervals] = "3PM-6PM"
)

learner_sg · Answer

johnt75&nbsp;thanks for the reply. But it does not work out in the intended manner.The result is given below. It selects all the timing it seems.&nbsp;My aim is to get the max values in the specified time and based on that select the time and day.&nbsp;&nbsp;&nbsp;&nbsp;

johnt75 · Answer

If I understand you correctly you want to get the time and day at which the max value occurs. You can do this with TOPN, likeTime of max value =
SELECTCOLUMNS (
    CALCULATETABLE (
        TOPN ( 1, measurement, measurement[sum_wc_consumption] ),
        REMOVEFILTERS ( measurement ),
        measurement[Parsed_Date]
            &gt;= TODAY () - 7
            &amp;&amp; measurement[Parsed_Date]
                &lt;= TODAY () - 6,
        measurement[Intervals] = "3PM-6PM"
    ),
    "@val", measurement[Time]
)

learner_sg · Answer

Hi&nbsp;johnt75&nbsp;,yes correct . I need to select 2 highest from each timezone..for this expression it is 3-6pm. 1 for each day(based on 6th and 7th day). But when I used ur expression it gives me the following error.&nbsp;

Forum Discussion

Error in selecting the highest values

6 Replies

Related content

2nd Highest Value

Find Highest Value of a Column

Highest value in a day

Create Measure to Return Highest Value

highest value by category