Power BI slicer OR condition

Question

The interaction between two slicers in Power BI gives me output with AND condition.

Example: If I selected the year 2020 and company ABC, the output would be all the data from company ABC in the year 2020. But I want the two slicers to work with OR condition.

I have used this Dax

Include = (MAX(Table1[Column1]) = SELECTEDVALUE(Col1[Column1])) +
  (MAX(Table1[Column2]) = SELECTEDVALUE(Col2[Column2]))

But the problem with above Dax I have not selected anything in slicer ( ALL by default) it is showing me a blank visual. What am I doing wrong?

mkRabbani mkRabbani · Accepted Answer · 2020-08-04T09:03:35

let me guess you have a table "or_slicer_main_table" with Year, Company and some other columns. Now create 2 new table where the first one will contain the distinct list of Year from table "or_slicer_main_table" and the second one will contain distinct Company list from that same table.

New custom Table 1:

or_slicer_year_list = 
SELECTCOLUMNS(
    'or_slicer_main_table',
    "YEAR", 'or_slicer_main_table'[year]
)

New custom Table 2:

or_slicer_company_list = 
SELECTCOLUMNS(
    'or_slicer_main_table',
    "company", 'or_slicer_main_table'[company]
)

Do not establish any relation between those 3 tables.

Step-1: Create Year slicer using the newly created "or_slicer_year_list" table.

Step-2: Create Company slicer using the newly created "or_slicer_company_list" table.

Step-3: Create these following 5 measures in your table "or_slicer_main_table"

1.

year_current_row = max('or_slicer_main_table'[year])

2.

year_selected_in_slicer = SELECTEDVALUE(or_slicer_year_list[YEAR])

3.

company_current_row = max('or_slicer_main_table'[company])

4.

company_selected_in_slicer = SELECTEDVALUE(or_slicer_company_list[company])

5.

show_hide = 
if(
    [year_selected_in_slicer] = [year_current_row] 
        || [company_selected_in_slicer] = [company_current_row], 
    1, 
    0
)

Now you have all instruments ready for play. Create your visual using columns from the table "or_slicer_main_table"

Final Step: Now just add a visual level filter for the measure "show_hide" and set value will show only when "show_hide = 1".

The final output will be something like below image-

Power BI slicer OR condition

2 Answers