Forum Discussion

DJSwezey's avatar
DJSwezey
Helper I
2 years ago
Solved

Filter Measure based on Text Value in Measure

I have a profit and margin table where the margin is a measure. I want to create a slicere where I can select 1 of 2 options: "Positive Margin" or "Negative Margin" where positive margin returns all Quarters where Margins that are greater than 0 and Negative Margin returns all quarters where margins are less than 0. 

 

 

Need Help
  • speedramps's avatar
    speedramps
    2 years ago

    Click here to download solution 

     

    How is works ...
    Create a option pick list table and add a single selection slicer

     

    Create a measure that returns 1 when the output matches the slicer option

     

    IsOption = 
VAR selectedoption = SELECTEDVALUE('Option picklist'[Option])
RETURN
SWITCH(TRUE(),
    selectedoption = "All Margins",1,
    selectedoption = "Positive Margins" && [Margin%] >= 0,1,
    selectedoption = "Negative Margins" && [Margin%] < 0,1
)

     

     

    Add a filter must = 1 to your repport visual

    Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the thumbs up and the [accept as solution] button.  Thnak you.

     

4 Replies

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    Click here to download solution 

     

    How is works ...
    Create a option pick list table and add a single selection slicer

     

    Create a measure that returns 1 when the output matches the slicer option

     

    IsOption = 
VAR selectedoption = SELECTEDVALUE('Option picklist'[Option])
RETURN
SWITCH(TRUE(),
    selectedoption = "All Margins",1,
    selectedoption = "Positive Margins" && [Margin%] >= 0,1,
    selectedoption = "Negative Margins" && [Margin%] < 0,1
)

     

     

    Add a filter must = 1 to your repport visual

    Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the thumbs up and the [accept as solution] button.  Thnak you.

     

    • DJSwezey's avatar
      DJSwezey
      Helper I
      2 years ago

      Perfect! This worked on the most granular level of my table, but not sure it works if I drill up.

      You can drill down quarter into state and then into city.

      When I filter by "Positive" or 'negative" at this level things work great.

      If I drill up then it looks like the filter doesnt work 

       

       

       

  • mussaenda's avatar
    mussaenda
    Community Champion
    2 years ago

    Hi DJSwezey ,

     

    You can create a separate tab;e with no any relationship with the categories you want to show in slicer and create a measure with switch function or if else with the logic that you explained above.

     

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    One question per ticket please!

     

    If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the thumbs up and the [accept as solution] button.  Thank you.