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 (which also contains this measure) based on what users are indicating in the concerning slicer. Anyone suggestions how to solve this? Thx! Christiaan

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 SlicerValueSlow Mover 0Fast 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.

Cannot use measure in slicer | Microsoft Fabric Community

22 Replies

v-jiascu-msft
Microsoft 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
  Helper 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
  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
  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
Greg_Deckler
Community Champion
9 years ago
Try re-engineering your measure as a calculated column. If you can post your data, or a sample, perhaps we can find a solution.
- Chris741
  New Member
  9 years ago
  Thanks for your response.
  
  I have set up a measures to perform a Pareto analysis. I dont think calculated columns will provide a solution, but perhaps I am wrong. Below the formulas
  
  Measures to perform and shows steps Pareto analysis:
  SalesUnits_Rank = RANKX(ALLSELECTED(vw_Pareto2);[Sum of SalesUnits])
  SalesUnits_Cum = SUMX(TOPN([SalesUnits_Rank];ALLSELECTED(vw_Pareto2);[Sum of SalesUnits]);[Sum of SalesUnits])
  SalesUnits_Share = DIVIDE([Sum of SalesUnits]; [Grand_Total_SalesUnits]; BLANK())
  SalesUnits_CumPerc = [SalesUnits_Cum]/[Grand_Total_SalesUnits]
  Pareto_BM = 0,80
  
  Pareto_SalesUnits_Name = IF([SalesUnits_CumPerc]>[Pareto_BM];"Slow mover";"Fast mover")
  
  I want to filter the table section in Power BI on results Pareto_SalesUnits_Name >> result Pareto_SalesUnits_Name
- Anonymous
  Not applicable
  8 years ago
  after a year u're answer was very helpful for me :D
azarel
Frequent Visitor
6 years ago
This is a major issue I've come across as the only solution for filtering numbers with a direct input field is to cast them as text. Because our data is only available through a live data gateway through the Power BI service the only option for altering data types and creating customs "columns" are measures.

R and Custom Columns and even custom tables are off the table because of how Power BI locks down additional datasets when you're connecting to Power BI service datasets. It's a real pain. I have a dataset with a unique numeric column and I'd like the users to be able to just type in the number.
Anonymous
Not applicable
7 years ago
Is it possible to create a slicer for multipal measures selection (to pick one each time, from the same table)?
- StidifordN
  Helper III
  7 years ago
  Also curious if this method can be used with 3 items... in my case "Non Compliant", "Compliant" and "< $100k".
  
  The Non Compliant works perfect. This is Value 2 in the created table, Value 3 is 'Compliant' - where does this fit into the formula... if it can.
  
  Measure99 = IF(HASONEVALUE('CompliantSlicer'[ComplianceFlag]), IF(VALUES(CompliantSlicer[Value])=2, IF([CurrentCompliantYN_V2]="Non Compliant","Non Compliant",blank()), IF([CurrentCompliantYN_V2]="Non Compliant",BLANK(),"N/A")), IF([CurrentCompliantYN_V2]="Non Compliant","Non Compliant","N/A"))
Anonymous
Not applicable
6 years ago
Hello all,
How to apply Slicer on Measures when connection is Live connection and connecting to SSAS MDX.

I have a Calculated measure called Available (It has value Int and basically It says no of stock available in Stores ). If stock is less than or equal to zero then will say stock not available else available and want a slicer for this.

Is this can be done in power BI using live connection to SSAS Multidimensional cube?

I know I can add a dimension in cube and can achieve this but that is big change for us. looking if something can be done on Power BI Side.