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.
Please refer to my steps.
Step 1 : Create two tables that contain all the Business Role Domains.
SlicerForTableLeft = SUMMARIZE('Table','Table'[Business Role Domain])
SlicerForTableRight = 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] = _SelectedBussinessInLeftSlicer
|| '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] = _SelectedBussinessInLeftSlicer
|| '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
Be the first to comment