Return text values from Calculated column by filtering in DAX

[pratnappagol12]

Hi,

Below is the dataset I am working, 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. Any hep is appreciated.

Regards,

Priyanka 

[v-cazheng-msft · Accepted solution]

Hi @pratnappagol12 ,

You may try this solution.

1 Create two tables that contains all the Business Role Domains

Business Role Domain
Corporate Finance
Sales Planning
Supply

2 Use Business Role Domain columns from these two tables to create a Slicer separately

3 Create these Measures

SelectedBussinessInLeftSlicer = SELECTEDVALUE ( SlicerForTableLeft[Business Role Domain] )

SelectedBussinessInRightSlicer = SELECTEDVALUE(SlicerForTableRight[Business Role Domain])

FormatRowsInLeftTable =

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 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"

    )

4 Conditional formatting rows in Table visuals. The result should look like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[edhans]

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.