Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dp106
Helper I
Helper I

Slicers with OR Logic

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

 

 

 

1 ACCEPTED SOLUTION

@dp106,

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

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) 
  

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
loewenherz93
New Member

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
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Here is an example 

 

Data

FirstNameLastNameCityCountry
TimCookParisFrance
JamesAndersonChicagoUnited States Of America
AdamsBrianBrusselsBelgium
NancyYoungBerlinGermany
KingThomasLondonEngland

 

Filter  Example 1

FirstNameLastNameCityCountry
Tim  Germany

 

Result

FirstNameLastNameCityCountry
TimCookParisFrance
NancyYoungBerlinGermany

 

Filter Example 2

FirstNameLastNameCityCountry
Adams berlinAmerica

 

Result

FirstNameLastNameCityCountry
AdamsBrianBrusselsBelgium
JamesAndersonChicagoUnited States Of America
NancyYoungBerlinGermany

 

Filter Example 3

FirstNameLastNameCityCountry
KingYoung belgium

 

Result

FirstNameLastNameCityCountry
KingThomasLondonEngland
NancyYoungBerlinGermany
AdamsBrianBrusselsBelgium

 

Thanks

@dp106,

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

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) 
  

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors