Get value based from same data and remark the value

[vividarinda]

I currently have data like this, 

But, how can I to set remark high and low based on date?

Thanks

[Rohit11 · Accepted solution]

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]

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

Thanks.. 

[vividarinda]

Hi @Rohit11 , Link here : https://shorturl.at/vALPV

[Rohit11 · Accepted solution]

@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]

@vividarinda Great !! Please accept this as a solution if it helped you!!!

[vividarinda]

Hi @Rohit11 ,

Thanks a lot. It works 😁

[vividarinda]

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 · Accepted solution]

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,