Error in selecting the highest values

[Learner_SG]

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")

[v-xiaotang]

Hi @Learner_SG 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[johnt75]

Try

3-6PM highest =
CALCULATE (
    MAX ( measurement[sum_wc_consumption] ),
    REMOVEFILTERS ( measurement ),
    measurement[Parsed_Date]
        >= TODAY () - 7
        && measurement[Parsed_Date]
            <= TODAY () - 6,
    measurement[Intervals] = "3PM-6PM"
)

[Learner_SG]

@johnt75 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. 

My aim is to get the max values in the specified time and based on that select the time and day.

[johnt75]

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, like

Time of max value =
SELECTCOLUMNS (
    CALCULATETABLE (
        TOPN ( 1, measurement, measurement[sum_wc_consumption] ),
        REMOVEFILTERS ( measurement ),
        measurement[Parsed_Date]
            >= TODAY () - 7
            && measurement[Parsed_Date]
                <= TODAY () - 6,
        measurement[Intervals] = "3PM-6PM"
    ),
    "@val", measurement[Time]
)

[Learner_SG]

Hi @johnt75 ,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.

[johnt75]

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 like

TOPN ( 1, measurement, measurement[sum_wc_consumption], DESC, measurement[Parsed_Date], ASC, measurement[Time], ASC ),