Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

slicer logic (OR | AND)

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

     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's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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.

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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's avatar
        adharia
        Frequent Visitor
        8 years ago

         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.