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, 

7 Replies

  • Rohit11's avatar
    Rohit11
    Resolver II
    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, 

     

  • vividarinda's avatar
    vividarinda
    Helper II
    2 years ago

    Hi Rohit11 ,

    Thanks for your feedback. But in my row data display result like this, in the same date. Could you help me?

     

    Vivid

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