Slicer with AND condition in Power BI instead of OR

[njg]

Hello, the default behaviour of the slicer in Power BI is that it shows the result of OR when you select multiple items. For example, if you have a slicer for software, and then you select both “AutoCAD" and "Revit" the result would list the staff that know "Revit" OR "AutoCAD".  I would like to see the list of staff who know both "Revit" AND "AutoCAD".  I have found the answer to this a couple of different ways by googling it, but, I am embarassed to say i don't understand the solutions.....i am hoping for a severely simplified answer if possible.

[Anonymous]

Hello,

I apologize for posting on a topic that has already been resolved. However, I am in need of your assistance. I have arrived at this place with the same problem, but I am getting the following error even after creating the same situation that can be confirmed.

I have established a many-to-many relationship between dim_product[Fruit] and fact_product[Fruit], but do you know what is missing?

dim_product

fact_product

relationship

[njg]

Hi, does this help?

If not, I can get you more info when i am back on may 24th (can's show my 'real' data, but can make up the Apple, pear, banana one again easy enough)

[Anonymous]

Thank you for your response. However, in my environment, it is not possible to have many-to-one or one-to-many relationships, even though many-to-many relationships are possible. Thank you for taking the time to help me, and I look forward to your return to your home country.

[Anonymous]

Thank you for your help with my previous inquiry regarding Power BI. I was able to resolve the issue on my own. The problem was that the dim_product[Fruit] column had multiple entries for each of the fruits (Apple, Banana, Pear). By consolidating them into individual entries, I was able to establish a many-to-one relationship. Your assistance was greatly appreciated. Thank you so much.

[njg]

Hi, I am glad you were able to resolve it.....I see in your first post that if i had paid better attention i would have caught that for you....will do better next time 🙂

[Jeanxyz]

Thank you for accepting the solution. I have so far mostly using the community to post my questions. Your feedback will encourage me to offer more solutions in future. 

[njg]

Hi, thank you so much for your time, but i am still a bit stuck (and you did a wonderful amazing job of keeping it simple for me).....i got this error message:

I created a 'many to many' relationship between these two tables 'fruit to fruit' (it wouldn't let me do a 'one to many' or a 'many to one')

my two tables look like this

and the fields are set up like this

I just know i am soooo close to having this right, really, really appreciate your help!

[MahyarTF]

Hi,

As I understand, you want to see the staffs who have experience in Autocad and Revit,

you could use Table Visual on the same page and as you filter your slicer on both values, there is the staff with whatever you want.

another way, in the particular visual or page, you could put the specific column (has experience title as Autocad, Revit,...) in your filter pane

[njg]

Hi, thank you for your reply, but this is not what I am looking for (my fault, i did not supply enought information)
Here is a sample table:

When I use the slicer to select Apple and Banana, i would like it to only show those who have BOTH Apples and Bananas, currently it shows those who have Apples OR Bananas

I am hoping for Bobby to not show up since he does not have both apples AND bananas

[MahyarTF]

Hi,

not sure if it is useful for you :

- I create another DAX table as below : 

Sheet63Table =

             SUMMARIZECOLUMNS(Sheet63[Name],

--                                "Expeience-Complete", CONCATENATEX(Sheet63, Sheet63[Experience] &", "),

                                "ExpeienceConcatCol", left(CONCATENATEX(Sheet63, Sheet63[Experience] &", "), len(CONCATENATEX(Sheet63, Sheet63[Experience] &", "))-2)

                                )

- then create relation between new table and existing one :

- then use the particular column as a slicer and table visual :

[njg]

Hi, Thank you for your help, but it's still not quite what i am hoping for.  Please see my 'apple, banana'pear' scenario above.....do you know how i would adapt this DAX to work in that scenario?

[Jeanxyz · Accepted solution]

Thanks for sharing this Dax formula. 

Here is the modified version,

Measure with and-only condition =

var selectedProduct=values(dim_Product)

var countRowsFact=countrows(distinct(filter(fact_product,related(dim_product[Fruit]) in selectedProduct)))

var countRowsProduct=countrows(selectedProduct)

return

if(countRowsFact>=countRowsProduct,countRowsFact,blank())

Please make sure there is relationship between Dim_Product and Fact_Product Table. The measure works on my side. 

Pbix sample: https://www.dropbox.com/preview/slicer%20with%20and%20condition.pbix?role=personal

[njg]

YAY!  THANK YOU, THANK YOU, THANK YOU!  It works beautifully.