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

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'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.