cancel
Showing results 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

## How to achieve 'or' logic but not 'and' logic with slicers

Scenario:
As we all know, when we have multiple slicers on our report or perform multiple selections in the same slicer, Power BI will perform logic and operations based on our selections and display the results of the operation in the view. So how do we achieve logic or effect with slicer? And how do you get the result of logically summing, or manipulating the options in slicer?

Part 1: Or in the slicer

The data of the table is shown above, how to get a union of data corresponding to two slicers
For example: get the data of the priority or the severity.

Step 1: Calculated table is static so it-which isn't change based on slicers. So we can create 2 tables that have nothing to do with it.

• or severity = VALUES('Or table'[severity])
• or priority = VALUES('Or table'[priority])

Step 2: Create a measure and add it to a visual-level filter.

Search: Returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-insensitive and accent sensitive.
More details: Search

Concatenatex: Concatenates the result of an expression evaluated for each row in a table.
More details: CONCATENATEX

or

``````measure =
VAR s1 =
CONCATENATEX ( 'or priority', [priority], "," )
VAR s2 =
CONCATENATEX ( 'or severity', [severity], "," )
RETURN
IF (
SEARCH ( MAX ( 'or Table'[priority] ), s1, 1, 0 ) > 1
|| SEARCH ( MAX ( 'or Table'[severity] ), s2, 1, 0 ) > 0,
1,
0
)``````

Step 3: filter the measure.

The result is as follows:

Part 2: Complete 'or' and 'and' with slicers
I need 2 visuals from the same table with 2 different filters.If I select Corporate Finance from filter 1 and Sales Planning from Filter 2,I should get the data accordingly in the visual and the common data needs to be highlighted.
Step 1 : Create two tables that contain all the Business Role Domains.

``````SlicerForTableLeft = SUMMARIZE('Table','Table'[Business Role Domain])

Step 2: create 2 measures.

``````FormatRowsInLeftTable =
VAR _SelectedBussinessInLeftSlicer =
SELECTEDVALUE ( SlicerForTableLeft[Business Role Domain] )
VAR _SelectedBussinessInRightSlicer =
SELECTEDVALUE ( SlicerForTableRight[Business Role Domain] )
VAR CountSelected =
CALCULATE (
COUNT ( 'Table'[Business Role Domain] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Business Role ID] ),
|| 'Table'[Business Role Domain] = _SelectedBussinessInRightSlicer
)
)
VAR CurRowEqualsLeft =
SELECTEDVALUE ( 'Table'[Business Role Domain] ) = _SelectedBussinessInLeftSlicer
VAR LeftEqualsRight = _SelectedBussinessInLeftSlicer = _SelectedBussinessInRightSlicer
RETURN
IF (
( CurRowEqualsLeft && LeftEqualsRight )
|| ( CountSelected >= 2
&& CurRowEqualsLeft ),
"Yellow",
"White"
)``````

``````FormatRowsInRightTable =
VAR _SelectedBussinessInLeftSlicer =
SELECTEDVALUE ( SlicerForTableLeft[Business Role Domain] )
VAR _SelectedBussinessInRightSlicer =
SELECTEDVALUE ( SlicerForTableRight[Business Role Domain] )
VAR CountSelected =
CALCULATE (
COUNT ( 'Table'[Business Role Domain] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Business Role ID] ),
|| 'Table'[Business Role Domain] = _SelectedBussinessInRightSlicer
)
)
VAR CurRowEqualsLRight =
SELECTEDVALUE ( 'Table'[Business Role Domain] ) = _SelectedBussinessInRightSlicer
VAR LeftEqualsRight = _SelectedBussinessInLeftSlicer = _SelectedBussinessInRightSlicer
RETURN
IF (
( CurRowEqualsLRight && LeftEqualsRight )
|| ( CountSelected >= 2
&& CurRowEqualsLRight ),
"Yellow",
"White"
)``````

Step 3: Conditional formatting rows in Table visuals.

The result is as follows.

Is not today's calculation interesting? Hope the above article helps.

Author: Polly

Reviewer: Kerry Wang & Ula Huang