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.

     

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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's avatar
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