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