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.

     

     

     

azarel's avatar
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.