Forum Discussion

vividarinda's avatar
vividarinda
Helper II
2 years ago
Solved

Get value based from same data and remark the value

I currently have data like this,    But, how can I to set remark high and low based on date?   Thanks
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Rohit11's avatar
    Rohit11
    2 years ago

    Hi vividarinda 

    Try this DAX expression using calculated column, 

     

    Calculated Column =

    VAR CurrentDate = 'Table'[Date]
    VAR MaxValue = CALCULATE(MAX('Table'[Value]), FILTER('Table', 'Table'[Date] = CurrentDate))
    VAR MinValue = CALCULATE(MIN('Table'[Value]), FILTER('Table', 'Table'[Date] = CurrentDate))
    RETURN
        IF('Table'[Value] = MaxValue, "High", IF('Table'[Value] = MinValue, "Low", BLANK()))
     

     

     

    DAX expression using Measure, 

    Measure =

    VAR MaxValue = CALCULATE(MAX('Table'[Value]), FILTER(ALL('Table'), 'Table'[Date] = MAX('Table'[Date])))
    VAR MinValue = CALCULATE(MIN('Table'[Value]), FILTER(ALL('Table'), 'Table'[Date] = MAX('Table'[Date])))
    RETURN

        IF(
            SELECTEDVALUE('Table'[Value]) = MaxValue, "High",
                IF(SELECTEDVALUE('Table'[Value]) = MinValue, "Low",BLANK()))
     

     

    Make this setting if you are not able to view measure with blank values . Right click on date and choose show items with no data. 

     

     



    Don't forget to give thumbs up and accept this as a solution if it helped you!!!

     

    Thank you, 

     

  • Rohit11's avatar
    Rohit11
    2 years ago

    vividarinda 

    Thanks for sending over .pbix file.

    I've slightly modifed DAX expression for calculated column. 

    Try with this updated DAX 


    minmax =
    VAR CurrentDate = FORMAT(datatkphcontoh[calendar],"m/d/yyyy")
    VAR MaxValue = CALCULATE(MAX(datatkphcontoh[TKPH Site]), FILTER(datatkphcontoh, FORMAT(datatkphcontoh[calendar],"m/d/yyyy") = CurrentDate))
    VAR MinValue = CALCULATE(MIN(datatkphcontoh[TKPH Site]), FILTER(datatkphcontoh, FORMAT(datatkphcontoh[calendar],"m/d/yyyy") = CurrentDate))
    RETURN

        IF(datatkphcontoh[TKPH Site] = MaxValue, "High", IF(datatkphcontoh[TKPH Site] = MinValue, "Low", BLANK()))

     

    Don't forget to give thumbs up and accept this as a solution if it helped you!!!

     

    Thank you, 

Rohit11's avatar
Rohit11
Resolver II
2 years ago

vividarinda , I'm unable to see all columns in the new image you sent , if possible send .pbix file !  

 

Thanks.. 

    • Rohit11's avatar
      Rohit11
      Resolver II
      2 years ago

      vividarinda 

      Thanks for sending over .pbix file.

      I've slightly modifed DAX expression for calculated column. 

      Try with this updated DAX 


      minmax =
      VAR CurrentDate = FORMAT(datatkphcontoh[calendar],"m/d/yyyy")
      VAR MaxValue = CALCULATE(MAX(datatkphcontoh[TKPH Site]), FILTER(datatkphcontoh, FORMAT(datatkphcontoh[calendar],"m/d/yyyy") = CurrentDate))
      VAR MinValue = CALCULATE(MIN(datatkphcontoh[TKPH Site]), FILTER(datatkphcontoh, FORMAT(datatkphcontoh[calendar],"m/d/yyyy") = CurrentDate))
      RETURN

          IF(datatkphcontoh[TKPH Site] = MaxValue, "High", IF(datatkphcontoh[TKPH Site] = MinValue, "Low", BLANK()))

       

      Don't forget to give thumbs up and accept this as a solution if it helped you!!!

       

      Thank you, 

      • vividarinda's avatar
        vividarinda
        Helper II
        2 years ago

        Hi Rohit11 ,

        Thanks a lot. It works 😁