How to implement an OR relationship between slicers

[RustyVDN]

Hi Guys,

I have one table in the underlying dataset. There is an item, and a location that looks like this:

Item	LA	NY	ID
Football	No	No	Yes
Tennis Ball	Yes	Yes	Yes
Soccer Ball	Yes	No	No

I'm using Chiclet slicer, and I end up with this:

LA?

No  Yes

NY?

No  Yes

ID?

No  Yes

What is happening: 

When I combine slicers (LA = Yes; NY = Yes...) I get a list of items that show up at both places (Tennis Ball).

What I WANT to happen:

When I combine slicers (LA = Yes; NY = Yes...) I want a list of items that show up at either place (Tennis Ball, Soccer Ball)

It seems to me the logic is implementing 'AND', when I want it to implement 'OR'

Thanks guys!

[v-yuaj-msft]

Hi @RustyVDN ,

In fact, you create a measure in any table is the same. You only need to drag the measure to the visual filter of your original table.

Best Regards,

Yuna

[RustyVDN]

Hi Yuna,

Regarding Step 1:

I have completed this successfullly

Regarding Step 2:

I have complete the measure. But what table do I add the measure to?

Regarding Step 3:
I'm really not sure what you're doing here. Could you please provide further information, and elaborate further?

PS - Also note that when you select Yes for "NY" your filter also selects items that are No, thus not functioning correctly.

[v-yuaj-msft · Accepted solution]

Hi @RustyVDN ,

Based on your description, you can do some steps as follows.

1. create several calculated tables unrelated to the original table.

ID = SUMMARIZE('Table','Table'[ID])

LA = SUMMARIZE('Table','Table'[LA])

NY = SUMMARIZE('Table','Table'[NY])

2. create a measure and drag it to the filter of the table visual, then select "is 1"

Measure = IF(MAX('Table'[NY]) in VALUES('NY'[NY])||MAX('Table'[LA]) in VALUES(LA[LA])||MAX('Table'[ID]) in VALUES('ID'[ID]),1,0)

3. create several Chiclet slicers.

Result: (I neither use the "ID" slicer nor add the condition of ID into the measure in the following result. You can add it. )

the measure I have modified is as follows.

 Measure = IF(MAX('Table'[NY]) in VALUES('NY'[NY])||MAX('Table'[LA]) in VALUES(LA[LA]),1,0)

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RustyVDN]

Hi Yuna,

Thanks for this. But I'm not sure I know what you mean.

1. What is a calculated table?
2. How do I create one?
3. How many do I need?
4. What does "ID= SUMMARIZE('Table','Table'[ID])" mean and what should I do with it?

Kind regards,

[v-yuaj-msft]

Hi @RustyVDN ,

Sorry, I assume you know how to create these tables. Let me explain.

Please refer to this link to know how to create a calculated table.

Using calculated tables in Power BI Desktop - Power BI | Microsoft Docs

You only need to create three tables as mentioned before because the count of your filters is 3. 

About Q4, it is in order to extract all the distinct values in the ID column and form a new independent calculated table. ("Yes" and "No") You need to disable the relationship betwwen your slicers so we have to create some independent tables to create slicers. 

You can try it according to the steps I mentioned earlier.

If you are still confused about it, please feel free to let me know. 

Best Regards,

Yuna

[RustyVDN]

Hi Yuna,

Regarding Step 1:

I have completed this successfullly

Regarding Step 2:

I have complete the measure. But what table do I add the measure to?

Regarding Step 3:
I'm really not sure what you're doing here. Could you please provide further information, and elaborate further?

[parry2k]

@RustyVDN Actually I'm writing a blog post on a similar use case and will share once it is ready. you can subscribe to my website to get notified once this blog post is published.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.⚡