custom filter/Slicer

[Gaurav_84]

Hi Experts,

I am looking to create custom Filter/slicer. Right now i am using standard filter but i unable to select data in range. Eeverytime i have to click control button in order to select multiple selections. I need to create slicer/Filter where i can give range for example A/00001 to A/99999 or T/00001 to T/99999 

I am looking something like below slicer instead of date i can use above code.

Regards

Gaurav

[v-xiaotang · Accepted solution]

Hi @Gaurav_84 

it looks impossible in Desktop, currently filter does not support range selection for text type values, but you can take this workaround.

-

create the 2 columns

Left = LEFT('Table'[Code],1)

Right = RIGHT('Table'[Code],6)

result

Best Regards,
Community Support Team _Tang

[Jos_Woolley]

Hi,

You could have two slicers, one for the lower limit and one for the upper limit.

Create 2 new tables:

Lower Code =
SELECTCOLUMNS ( 'Table', "Lower Code", 'Table'[Code] )

Upper Code =
SELECTCOLUMNS ( 'Table', "Upper Code", 'Table'[Code] )

Then 2 new measures:

InCodeRange =
SUMX (
    'Table',
    0
        + (
            'Table'[Code] >= SELECTEDVALUE ( 'Lower Code'[Lower Code] )
                && 'Table'[Code] <= SELECTEDVALUE ( 'Upper Code'[Upper Code] )
        )
)

UpperCodeFilter =
SUMX (
    'Upper Code',
    0
        + ( 'Upper Code'[Upper Code] >= SELECTEDVALUE ( 'Lower Code'[Lower Code] ) )
)

Add two slicer visuals to your page, one referencing the Lower Code field and the other the Upper Code field.

Highlight the Upper Code slicer and, in the Filter pane, drag in the UpperCodeFilter measure and set the condition as 'is 1'.

Highlight the visual you wish to filter (or add as a page filter, as per your requirements) and, in the Filter pane, drag in the InCodeRange measure and set the condition as 'is 1'.

Regards

[TomMartens]

Hey @Gaurav_84 ,

unfortunately, this is not possible.

Currently, only columns with a date, datetime, or numerical datatype are able to leverage the range (between) slicer type.

You have to create an idea on ideas.powerbi.com to get this option enabled for text columns.

To overcome this limitation, you might consider adding two additional columns to the table that contains the column code, one that stores the text part and the other one stores the number, meaning 1 instead of 000001. Then you can use these new columns for the filtering and you can also use the between option for the number column.

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

[amitchandak]

@Gaurav_84 , as of now range slicer is possible for Number or date only