Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Max selected value in a slicer

Hi,

 

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

    

Week   Value
54445
636
7335
86
944
101414

 

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
54445no
636yes
7335no
86no
944no
101414no

 

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!

Show and Tell
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can use the following steps to meet your requirement:

     

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

     

    1. 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")

     

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

     

    BTW, pbix as attached.


    Best regards,

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We can use the following steps to meet your requirement:

     

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

     

    1. 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")

     

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

     

    BTW, pbix as attached.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,