Forum Discussion
Cannot use measure in slicer
- 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 12. 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.
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.
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.