Multi Selected Filter showing more values that it should

[Anonymous]

I currently have two tables in this dataset. 

Table 1:                                                                       Table 2:

Company Name            Location                                Company Name               Location Type

------------------           -----------------                      ------------------              ------------------

Breeze                           Chicago, Illinois                      Breeze                             Office

Alter                              Denver, Colorado                   Breeze                             Factory

                                                                                     Alter                                Office

It is a one-many relationship and I want to be able to filter all of our locations based off the location type. Currently if I select "Office" it shows Breeze and Alter locations. If I do multiselect and click "Office" and "Factory" it still shows both Breeze and Alter. I would only want it to show Breeze since this company contains both location types. Any ideas?

[v-robertq-msft]

Hi, @Anonymous 

According to your description, you want to make the table only shows “Breeze” when you select both location types, you can try my steps:

1. Create a measure in Table 2:

Measure =

var _1=CALCULATE(MAX([Company Name]),FILTER(all('Table 2'),[Location Type]="Office"))

return

IF("Office" in SELECTCOLUMNS('Table 2',"1",[Location Type])&&

COUNTX(ALLSELECTED('Table 2'),[Location Type])>2,

IF(MAX('Table 1'[Company Name ])=_1,1,0)

,1)

2. Apply this filter for the table visual:

And you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

With this example though if I select no filters it is only showing Breeze. I would like to be able to display all possible locations on a map so this would never show all results to my users. Not to mention when a location ends up having 5 different location types "Office", "Factory", "Warehouse", etc. not sure how your measure would hold up.

[Anonymous]

Hi @Anonymous 

Whatever you select in one field would filter what is against it in the other field, based on your thing, I think you can make a concatenated column say ex office-factory , office, factory in one column and whenever you select anything from this concatenated column the results would pop out based on your expectation.

Please mark this as solution if you think this can solve your problem buddy.

[Anonymous]

I get what you are going for but I dont think this would work. If I was to select "Office" in a filter then it would display all office locations. If I selected both "Office" and "Factory" it should show all locations that have an "Office" and "Factory" on-site. With your proposed filter idea there would be now way to anticipate what combination I am selecting once 5+ location types get thrown into the mix.

[Anonymous]

The issue with this is we have up to 10 different location types and some locations have up to 5 types. Would make the filter not user friendly. 

[Anonymous]

What about giving a same slicer and dragging one field over the other. Have you tried thay?

[Anonymous]

Not sure I get what you mean.