Forum Discussion

Chris741's avatar
Chris741
New Member
9 years ago
Solved

Cannot use measure in slicer

Hello,   I have created a measure which returns the text 'Slow' or 'Fast'. I want to use this measure in a slicer, but Power BI blocks this.   My objective is to filter the underlying table (whic...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hello Chris741,

     

    A measure can't be used in clicer directly. You can try this.

    1. Create a table named "ForSlicer" like this (sign 1 in the pic).

    Pareto_SalesUnits_Name    SlicerValue
    Slow Mover                           0
    Fast Mover                            1

    2. Create a measure as below. Maybe you need to make some change.
    Pareto_SalesUnits_Name =
    IF (
        HASONEVALUE ( 'ForSlicer'[Pareto_SalesUnits_Name] ),
        IF (
            VALUES ( 'ForSlicer'[SlicerValue] ) = 1,
            IF([SalesUnits_CumPerc]>[Pareto_BM],BLANK(),"Fast mover"),
            IF([SalesUnits_CumPerc]>[Pareto_BM],"Slow mover",BLANK())
        ),
        IF([SalesUnits_CumPerc]>[Pareto_BM],"Slow mover","Fast mover")
    )

    3. Create a clicer using column "Pareto_SalesUnits_Name" in table FORSLICER (sign 2 in the pic).

    4. Set the visual level filter of the measure "Pareto_SalesUnits_Name" to "is not blank". (sign 3 in the pic).

    5. It works though it's not perfect. Hope this would be a little help.

     

     

     

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hello Chris741,

 

A measure can't be used in clicer directly. You can try this.

1. Create a table named "ForSlicer" like this (sign 1 in the pic).

Pareto_SalesUnits_Name    SlicerValue
Slow Mover                           0
Fast Mover                            1

2. Create a measure as below. Maybe you need to make some change.
Pareto_SalesUnits_Name =
IF (
    HASONEVALUE ( 'ForSlicer'[Pareto_SalesUnits_Name] ),
    IF (
        VALUES ( 'ForSlicer'[SlicerValue] ) = 1,
        IF([SalesUnits_CumPerc]>[Pareto_BM],BLANK(),"Fast mover"),
        IF([SalesUnits_CumPerc]>[Pareto_BM],"Slow mover",BLANK())
    ),
    IF([SalesUnits_CumPerc]>[Pareto_BM],"Slow mover","Fast mover")
)

3. Create a clicer using column "Pareto_SalesUnits_Name" in table FORSLICER (sign 2 in the pic).

4. Set the visual level filter of the measure "Pareto_SalesUnits_Name" to "is not blank". (sign 3 in the pic).

5. It works though it's not perfect. Hope this would be a little help.

 

 

 

  • Srikanth7Gunnam's avatar
    Srikanth7Gunnam
    Icon for Helper I rankHelper I
    5 years ago

    Hello,

     

    I tried the above procedure but I was not able to figure out the measure in Step2. Can you please help me for the following scenario?

     

    I have a measure that results in text values like "Taken Care" or "Ignored". I would like to add that measure as a slicer. Can someone help me with this?

     

    Measure:

    Var TaskWorkingornot =
    CALCULATE([# Measure1],ALL('Table'[Task]))
    Return
    If(
    TaskWorkingornot = 0, "Ignored", "Taken Care"
    )
     
    Any help would be really appreciated.
     
    Thanks,
    Srikanth
  • Chris741's avatar
    Chris741
    New Member
    9 years ago

    v-jiascu-msft,

     

    Your solution works perfect!

     

    We also had already created an alternative, but sophisticated, solution in R. In the upcoming period we will share this with the community.

     

    Thanks again all for your quick responses.

     

     

     

  • davewardd10's avatar
    davewardd10
    Frequent Visitor
    8 years ago

    v-jiascu-msft I'm experiencing a similar issue although I cannot get your fix to work.

     

    Basically I have created a calculated measure which will display the Return on Advertising Spend (% value which can vary) - Essentially I'd like to slice this data so that, for example, it shows me only those items which have a Return on Advertising Spend of greater than 200% - any idea how I would do this?

     

    Kind regards,

     

    David

  • Tasos's avatar
    Tasos
    Icon for Helper II rankHelper II
    8 years ago

    Hello v-jiascu-msft,

     

    I have applied your technique and it worked for me as well. However, if I have understood well, I need to add the "measure" into the specific visual and cannot be applied as a page level filter. Is that correct?

     

    Hopefully, you still remember what you had done over a year ago.

     

    Thanks,

    Tasos

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Tasos,

       

      I'm afraid we can't add a measure in the Page Level filter. That won't be necessary. Because the context is the whole data model in the page level. There could be always only one value of the measure. You still can vote up this idea.

       

      Best Regards,

      Dale

  • dmalviya's avatar
    dmalviya
    Frequent Visitor
    8 years ago

    Hi 

     

    this solutions worked for me. thank you very much. 

     

    Now I want to display list of outlets which are only "Slow mover" from last 6 months. Please  suggest how can do that. 

     

    Thanks

    Deepak 

  • dmalviya's avatar
    dmalviya
    Frequent Visitor
    8 years ago

    Hi v-jiascu-msft, your solutions worked perfectly. thanks. 

     

    Now i want to show only those stores which "Slow mover" from last 6 month. please suggest approach. 

     

    Thanks

    Deepak 

  • dmalviya's avatar
    dmalviya
    Frequent Visitor
    8 years ago

    Hello 

     

    your solution works perfectly for me. Now i want to show list of stores which are "Slow mover" from last 6 months. How to do this?

  • dmalviya's avatar
    dmalviya
    Frequent Visitor
    8 years ago

    Hello v-jiascu-msft

     

    your solution worked perfectly for me. Now i want to display list of stores which are "Slow" continuously from last 6 month. Please suggest how to do this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Can this task be solved for three groups?

  • abbyyyyyy's avatar
    abbyyyyyy
    Regular Visitor
    7 years ago

    Hi, what if there are three types "fast mover","medium mover" and "slow mover"? How does that work?

     

    Thanks