How create custom FILTER?

[UsePowerBI]

Hello

I have a table visual:

A, 1 

A, 2

B, 1

etc

I want to create a filter visual (slicer) with the entries:

Category A

Category B

When I select the entry Category A, I want to filter the table for Col1 = A and Col2 = 1.

When I select the entry Category B, I want to filter the table for Col2 = B and Col2 =1.

Can you tell me please how to achieve this?

Do I need to create a measure first and add it as a slicer visual? 

What would be the code of the measure?

Or is there another way?

Thanks!

[Anonymous]

Hi @UsePowerBI,

First create a slicer table containing all the categories;

Then create 2 measures as below:

_column1 = CALCULATE(MAX('Table'[Column1]),FILTER('Table','Table'[Column1]=RIGHT(SELECTEDVALUE(Slicer[Column1]),1)))

_column2 = CALCULATE(MAX('Table'[Column2]),FILTER('Table','Table'[Column1]=RIGHT(SELECTEDVALUE('Slicer'[Column1]),1)&&'Table'[Column2]=1))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[UsePowerBI]

@Anonymous 

Thanks but I am not sure how to do the 'First create a slicer table containing all the categories;'

Can you elaborate please?

[amitchandak]

@UsePowerBI , Try like

Measure = 
Var _1 = if(selectedvalue(Table[slicer])="Category A",filter(Table,Table[Col1] = "A" && Table[Col2] = 1),filter(Table,Table[Col1] = "B" && Table[Col2] = 1))
return
calculate([measure],_1)

[UsePowerBI]

Thanks but what is Table[slicer]? Is it another MEASURE?

[amitchandak]

@UsePowerBI , You need to have a table with values

Assume : TableSlicer

Slicer

Category A

Category B

Measure =
Var _1 = if(selectedvalue(TableSlicer[slicer])="Category A",filter(Table,Table[Col1] = "A" && Table[Col2] = 1),filter(Table,Table[Col1] = "B" && Table[Col2] = 1))
return
calculate([measure],_1)