How to create a custom SLICER?

[Anonymous]

Hello

I have two sets of criteria, e.g.:

CriteriaSet1:

Col1 = A || Col2 <> B && Col3 = C

CriteriaSet2:

Col1 = D || Col2 <> E && Col3 = F

I want to use these criteria to filter a table. I then want to create a slicer which will have two values: CriteriaSet1 and CriteriaSet2.

When I select each of these values, I want to apply the respective criteria.

How can I do that? Do I need to create Measures, Tables or anything else? Please explain in your answer instead of just saying CriteriaSet1 = .... without explaining what that is.

Thanks!

[Anonymous]

Hi @Anonymous ,

You will need to create a new table with couluns contains measures' name.

Use this table as slicer with following measure.

Measure = SWITCH(SELECTEDVALUE('Table (2)'[slicer]),"sum",[sum],"avg",[avg])

Sample data for your reference.

 

Best Regards,

Jay

[Anonymous]

@Anonymous  thanks but can you tell me please what is the below and how do I create it? 

'Table (2)'[slicer]

Thanks! 

[Anonymous]

Hi @Anonymous ,

It's a simple table contains measures' name as the slicer.

Pbix as attached, hopefully works for you.

Best Regards,

Jay

[Anonymous]

@Anonymous thanks but unfortunately I cannot open your test.pbix, it displays an error and it closes

From what I understand, I will need to create both one table and one measure, is that right?

Can you paste please the code for the two and I will try to figure out.

Thanks!

[Anonymous]

@Anonymous 

I get this error:

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

[Anonymous]

@Anonymous 

Sorry I still do not understand what you are doing.

I have a table already that I want to apply the filter on.  I do not want to create another table. If I do need to create another table, I do not want it to be visible.

So I already have the table:

A, A1

B, B2

C, C3

D, D2

E, E3

I only want to create a slicer visual.

The slicer visual will need to have two values.

One value is: Show the Table rows where Col2 contains "2"

The second value is: Show the Table rows where Col2 contains "1"

These are the only filters I want to be visible in the slicer visual.

Can you advise what I need to do step by step please, assuming nothing as known?

Thanks!

[Anonymous]

Hi @Anonymous ,

avgx = AVERAGEX(ALL('Table'),'Table'[Column2])

sumx = SUMX(ALL('Table'),'Table'[Column2])

Measure = SWITCH(SELECTEDVALUE('Table (2)'[slicer]),"sum",[sumx],"avg",[avgx])

Table2 is a sample table which i create manually use Enter Data feature.

Best Regards,

Jay

[amitchandak]

@Anonymous , In case you need as in filter.

calculate([measure], filter(Table,(Col1 = D || Col2 <> E && Col3 = F) && (Col1 = A || Col2 <> B && Col3 = C)))

[Anonymous]

@amitchandak 

Thanks, can you elaborate how do I use that? Also what is the [measure] you mention?

[parry2k]

@Anonymous add a new custom column with the logic you want and use that for the slicer

New Column =
IF ( <condition1>, "Criteria 1", 
IF ( <condition2>, "Criteria 2",
"Unknown"
) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡