Max selected value in a slicer

[Anonymous]

Hi,

I have 2 columns. Week Number and Value so my data look like this

Week	Value
5	4445
6	36
7	335
8	6
9	44
10	1414

I have a slicer for Week number. What Im trying to do is take the maximum value selected on the slicer. So for example if the user selected the maximum value of the slicer to be 8 I want to take this value and subtract 2 out of it. and then create another measure to check if this number = the week number then return a yes else return a no.

The measures I created:

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

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

The desired output should be like this

Week	Value	measure test
5	4445	no
6	36	yes
7	335	no
8	6	no
9	44	no
10	1414	no

But its returning a "no" for all fields. And I realized that its not taking the max value of the selected slicer - 2 and compare it with all records, rather its subtracting 2 from every record and then compare it with the original number of that record before subtracting a 2.

Your help is highly appriciated.

Thank you!

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement:

1. Create a new table that contains unique week number.

2. We can create a measure to calculate “Yes” or “No”

measure test =

var select_slicer = SELECTEDVALUE('Table 2'[Week])

var week_2 = select_slicer-2

return

IF(week_2 = max('Table'[Week]) , "Yes","No")

3. At last use the Table2[Week] to be slicer, we can get the result like this,

BTW, pbix as attached.

Best regards,