slicer logic (OR | AND)

[Anonymous]

Hi!

when i select 2 items in slicer i want to see records that have both selected items.

table A:

id      value

1         q

2         w

3         e

table B:

id     table_A_id     value

1       1                   q1    

2       1                   q2

3       2                   q3

4       3                   q4

5       5                   q1

i want to filte table A by values in table B

so if a select q1 AND q2 i should see only table A id = 1 value = q

Any ideas if it is possible to make slicer work as logically AND?

[Phil_Seamark]

Hi @Anonymous,

You could combine the two tables into 1 table in the Query Editor or in DAX as one approach.

[Anonymous]

what will it give to me? i need to filter them dynamically in the dashboard

[Phil_Seamark]

You will be able to filter the data dynamically.  If you use your TableB[Value] field as a slicer, it will include any rows selected 

id	table_A_id	value	TableA_Value
1	1	        q1	q
2	1 	        q2	q
3	2	        q3	w
4	3	        q4	e
5	5	        q1	

[Anonymous]

---

 It seems that I can't describe well what i want)))) i changed you table a bit to make it clear:

id	table_A_id	value	TableA_Value
1	1	        q1	q
2	1 	        q2	q
3	2	        q3	w
4	3	        q1	e

---

now i'm trying to slice table with field value:

in slicer i select q1 & q2 not q3:

• q1 
• q2
• q3

so the result in power bi will be (filter works as logically OR):

id	table_A_id	value	TableA_Value
1	1	        q1	q
2	1 	        q2	q
4	3	        q1	e

 but i need so (wilter work as logically AND):

id	table_A_id	value	TableA_Value
1	1	        q1	q
2	1 	        q2	q

[Phil_Seamark]

I had some success with the base table (uncombined) using this measure as a filter on a Table visual.

So if you set this to be a table flter and set to "is = 1", then if you have a multi-select slicer it will behave like an AND filter.

If you are happy with that I can combine the tables and show it working with the Value

AND_Filter = 
var CountOfFiltersSelected = COUNTROWS(FILTERS('Table2'[value]))
var DistinctCountOfValue = DISTINCTCOUNT('Table2'[value])
Return IF
		(
		CountOfFiltersSelected = DistinctCountOfValue,
		1,
		0
		)

[adharia]

 Hi @Phil_Seamark,  can you please show it " I can combine the tables and show it working with the Value.

I am also stucked with similar issue where I have column with multiple entries of skills. I want to filter out using multi select slicer with the AND logic.

For Example, I want to see the Employee with both SQL & PowerBI(skills) on selecting it from slicer.

[Phil_Seamark]

Oh I get it.  Sorry.

I still think the combined table is the way to go but add a column that carries the value of 1.

Then a measure can be used to count the number of items selected by the slicer (in this case 2, and compare with the distinct number of values per ID).

I have to head out soon, so can give you a working example in a few hours.