How to achieve ‘or’ logic but not ‘and’ logic with…


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

vhenrykmstf_0-1664271090765.png

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.

vhenrykmstf_1-1664271222514.png

 

The result is as follows:

vhenrykmstf_2-1664271237985.pngvhenrykmstf_4-1664271273638.png

 

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.

vhenrykmstf_5-1664271440016.png

The result is as follows.

vhenrykmstf_6-1664271448507.png

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

 

Author: Polly

Reviewer: Kerry Wang & Ula Huang



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*