Slicers with OR Logic

[dp106]

Hi,

I have a flat table with 20+ columns and around 1 million records. Users need the ability to filter the data on around 10 columns with an OR condition.

Is this doable in power BI considering the no of records and how.

Thanks

[loewenherz93]

Good solution, Lydia. However, it does not work with multi-select filters. If I select several first names, then the table shows all items again. Do you know how to solve that?

[v-yuezhe-msft]

@dp106,

Could you please give an simple example describing the OR logic ?  You can share sample data of your table and post expected result here.

Regards,
Lydia

[dp106]

Here is an example 

Data

FirstName	LastName	City	Country
Tim	Cook	Paris	France
James	Anderson	Chicago	United States Of America
Adams	Brian	Brussels	Belgium
Nancy	Young	Berlin	Germany
King	Thomas	London	England

Filter  Example 1

FirstName	LastName	City	Country
Tim			Germany

Result

FirstName	LastName	City	Country
Tim	Cook	Paris	France
Nancy	Young	Berlin	Germany

Filter Example 2

FirstName	LastName	City	Country
Adams		berlin	America

Result

FirstName	LastName	City	Country
Adams	Brian	Brussels	Belgium
James	Anderson	Chicago	United States Of America
Nancy	Young	Berlin	Germany

Filter Example 3

FirstName	LastName	City	Country
King	Young		belgium

Result

FirstName	LastName	City	Country
King	Thomas	London	England
Nancy	Young	Berlin	Germany
Adams	Brian	Brussels	Belgium

Thanks

[v-yuezhe-msft · Accepted solution]

@dp106,

Firstly, create seperate FirstName table, LastName table, City table and Country table.


Secondly, create a calcualted column as below.

Column = Table2[FirstName] & ","& Table2[LastName]& ","&Table2[City]& ","&Table2[Country]

Thirdly, create the following measure in your table. Drag the measure to visual level filter and set its value to 1. For more details, please check attached PBIX file.

checkmeasure = var selectfirst= IF(
	ISERROR(
		SEARCH(SELECTEDVALUE(Firstname[FirstName],1), FIRSTNONBLANK(Table2[Column],1))
	)
	,
	0,
	1
)
var selectcity=IF(
	ISERROR(
		SEARCH(SELECTEDVALUE(City[City],1), FIRSTNONBLANK(Table2[Column],1))
	)
	,
	0,
	1
)
var selectlast= IF(
	ISERROR(
		SEARCH(SELECTEDVALUE(Lastname[LastName],1), FIRSTNONBLANK(Table2[Column],1))
	)
	,
	0,
	1
)
var selectcountry= IF(
	ISERROR(
		SEARCH(SELECTEDVALUE(Country[Country],1), FIRSTNONBLANK(Table2[Column],1))
	)
	,
	0,
	1
)
return

IF(selectcity=1 ||selectcountry=1 ||selectfirst=1 ||selectlast=1,1,0) 
  

Regards,
Lydia

[E12345]

Hi Lydia, thank you so much for providing the solution.
However, this solution works only when the user selects a single option from the slicer - it does not work when more than one selection is made from any of the slicers - the chart shows blanks when I select several options from the same slicer. Is there a work around to this? 
Thanks!