Forum Discussion

Learner_SG's avatar
Learner_SG
Icon for Helper IV rankHelper IV
4 years ago

Error in selecting the highest values

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]),...
Need Help
Learner_SG's avatar
Learner_SG
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
    Learner_SG
    Icon for Helper IV rankHelper IV
    4 years ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

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