Forum Discussion

dp106's avatar
dp106
Icon for Helper I rankHelper I
7 years ago
Solved

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      dp106
      Icon for Helper I rankHelper I
      7 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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

  • loewenherz93's avatar
    loewenherz93
    New Member
    5 years ago

    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?