Capture the maximum selected value of a slicer and use it in another measure

[Anonymous]

Hi,

My data has 2 columns. Week number and value.

week	value
1	32
2	52
3	32
4	3
5	4445
6	36

I have a slicer for week number. What Im trying to do is capture the maximum value of the slicer, so for example if the user selected the range of the slicer to be from 2 to 5. I want to take the 5 and subtract 2 from it, and then compare the answer with my table and put a yes whenever the answer = the week column value.

I have created 2 measures:

Measure 2 = MAX(Sheet1[week])-2

measure test = IF(measure 2 = max(Sheet1[week]) , "Yes","No")

The desired output should look like this:

week	value	Measure Test
1	32	no
2	52	no
3	32	yes
4	3	no
5	4445	no
6	36	no

But what Im getting instead is that all values are getting a "no". What I fugured is that instead of capturing one value (max slicer - 2) and comparing it with all records, its actually subtracting 2 from each record and then compare it with the original record.

Your help is highly appreciated.

Thank you

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

Just adjust the [measure 2] as below:

Measure 2 = CALCULATE(MAX(Sheet1[week]),ALLSELECTED(Sheet1))-2

Result:

Regards,

Lin

[amitchandak]

Refer

measure test = 
var _max =MAXX(Sheet1,Sheet1[week])-2
return
IF( max(Sheet1[week]) =_max, "Yes","No")

[Anonymous]

Hi amitchandak ,

I have used the suggested measure but its still giving "no" for all records. As you can see in the screenshots below: