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.

 

 

 

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