Multiple values to filter data

[bnjmnnl]

Good day all, 

There is some challenge to get the right slicers to filter my data. In my dataset there are contracts for transporters to where they will load (LD) or deliver (DL) a order. In one contract there can be more load or delivery locations. I'll put an example below:

Contract	Location	load or delivery
101	NLD	LD
101	NLD	DL
101	BEL	LD
102	UK	DL
102	UK	DL

Above is an example dataset. For contract 101 there will be a load in BEL and NLD and a delivery in NLD. This are not if's, they are fixed deliveries and loads. So if we book contract 101 we have those three steps. If we need less then those three steps to complete the requested transport, we will simply book by another contract containing the needed steps. 

My issue is:
I want the user to select on what locations he/she needs to book an order, and then see all the possible contracts that can arrange that transport. The thing is, when I select for example NLD in the slicer, the dataset will be filtered for NLD en therefore the rows with BEL will disapear. Is there a way to filter multiple rows. 

The only thing I can think of is to have the user select the locations in 1 slicer but this will result in an OR filtering. If the user selects NLD and BEL in de slicer(s) I need to only show options of contracts that contain both those values. Also If there are three NLD locations in one contract the user need to select it three times. 

If you may have a solution, please feel free to assist me!
Thank you in advance! 🙂

[danextian · Accepted solution]

Hi @bnjmnnl ,

The logic in the attached sample pbix checks whether the load and delivery slicer selections match the locations in the fact table.

[lkalawski]

Hi @bnjmnnl ,

Analyzing what you wrote, you want the user to be able to select Load or Delivery Location and see all contracts that apply to a given location.

Proposed solution:
1. I would bring the table to this form. Thanks to this, you have a simple division of where to deliver from.

2. To achieve the effect you mentioned, you need to have two additional tables that will be such a dictionary - a combination of the contract and load location or delivery location.

Load Location: 

Delivery Location:

3. Having these two tables, you should connect them via Contract ID to the main table.

4. And then create two slicers that will help the user find the right order.

_____

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin

[danextian]

I find these statements contradicting:

• The thing is, when I select for example NLD in the slicer, the dataset will be filtered for NLD en therefore the rows with BEL will disapear. Is there a way to filter multiple rows. 
• The only thing I can think of is to have the user select the locations in 1 slicer but this will result in an OR filtering.

Or do you mean, you want to find a contract  that contains the locations based on slicer selection. For example if the user selects NLD but not BEL then there's no match unless BEL is selected as well?

[lbendlin]

It's a standard "Filtering up"  pattern.

What you really want is when your user chooses "NLD"  you want to look up which contracts contain NLD, and then show all the countries for these contracts.

This requires a disconnected table to feed the country slicer, and a measure as a visual filter.

[bnjmnnl]

The result should be:

User selects Loading in NLD, user gets all the contracts that contain a option with loading in NLD. 
If user selects delivery in NLD and delivery in BEL, user only gets contracts that cointain a delivery in NLD AND in BEL. All the options that only have NLD will not be shown. 

But the two issues I'm facing now are:

If I let the user select both locations in 1 slicer, it will function as an OR statement. So he will get all the option with NLD or BEL or both. 

If I let the user use two or more slicers, all the data will be filtered through the first slicer. So if the user uses the first slicer to put NLD there will be no data left to filter a BEL contract. Did I explain this well enough? If not please ask me!

[lbendlin]

If user selects delivery in NLD and delivery in BEL, user only gets contracts that cointain a delivery in NLD AND in BEL.

There is a conflict between natural language and programming language. In programming speak this is

in NLD OR in BEL

In Power BI all filters inside a column are OR filters (equivalent of "IN list"), but across columns they are always AND filters (applied on top of each other). You need to spend extra effort to make filters across columns behave in OR fashion. And you need to spend extra effort if you want the filters inside a column to be treated as AND.

[bnjmnnl]

A transport arranged can have multiple delivery locations and do not need a loading for that. But the user needs to be able to select Delivery in NLD & BEL & IRL for example to get the result of all the contracts that contain all those locations for delivery. He/she then can check for the preferred option. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...