Cannot use measure in slicer

[Chris741]

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

[azarel]

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]

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. 

[Anonymous]

Is it possible to create a slicer for multipal measures selection (to pick one each time, from the same table)? 

[StidifordN]

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"))

[v-jiascu-msft · Accepted solution]

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]

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

[Gavarse]

Hi All

I created a Slicer table FMSSlicer

I have a 2 measure based on Sales history

Sales Hit = CALCULATE(DISTINCTCOUNT(Sales[SKU+Fin_year+Period])+0,FILTER('Date','Date'[Fin_year]))

FMS = if(Sales[Sales Hit]=0,"X",if(Sales[Sales Hit]<=3,"Slow",if(Sales[Sales Hit]<=7,"Medium","Fast")))

I need to create another measure so that Slicer table can be used, Can somebody help on this. Thanks

[abbyyyyyy]

Hi, what if there are three types "fast mover","medium mover" and "slow mover"? How does that work?

Thanks

[Anonymous]

Can this task be solved for three groups?

[dmalviya]

Hello v-jiascu-msft

your solution worked perfectly for me. Now i want to display list of stores which are "Slow" continuously from last 6 month. Please suggest how to do this.

[dmalviya]

Hello v-jiascu-msft

your solution works perfectly for me. Now i want to show list of stores which are "Slow mover" from last 6 months. How to do this?

[dmalviya]

Hi v-jiascu-msft, your solutions worked perfectly. thanks. 

Now i want to show only those stores which "Slow mover" from last 6 month. please suggest approach. 

Thanks

Deepak 

[v-jiascu-msft]

Hi Deepak, @dmalviya

I would suggest you open a new thread in this forum with a sample data. Your new topic is different from this one. 

Best Regards,

Dale

[dmalviya]

Hi v-jiascu-msft

New thread is here - https://community.powerbi.com/t5/Desktop/List-of-Customers-continuously-follwing-rating/m-p/487483

Thanks

Deepak

[dmalviya]

Hi v-jiascu-msft

this solutions worked for me. thank you very much. 

Now I want to display list of outlets which are only "Slow mover" from last 6 months. Please  suggest how can do that. 

Thanks

Deepak 

[Tasos]

Hello @v-jiascu-msft,

I have applied your technique and it worked for me as well. However, if I have understood well, I need to add the "measure" into the specific visual and cannot be applied as a page level filter. Is that correct?

Hopefully, you still remember what you had done over a year ago.

Thanks,

Tasos

[v-jiascu-msft]

Hi @Tasos,

I'm afraid we can't add a measure in the Page Level filter. That won't be necessary. Because the context is the whole data model in the page level. There could be always only one value of the measure. You still can vote up this idea.

Best Regards,

Dale

[davewardd10]

@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

[Chris741]

@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.

[Greg_Deckler]

Try re-engineering your measure as a calculated column. If you can post your data, or a sample, perhaps we can find a solution.