Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Return text values from Calculated column by filtering in DAX

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.

 

pratnappagol12_0-1646148194638.png

Regards,

Priyanka 

 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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

vcazhengmsft_0-1646380083173.png

 

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.

vcazhengmsft_1-1646380083177.png

 

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

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

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

vcazhengmsft_0-1646380083173.png

 

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.

vcazhengmsft_1-1646380083177.png

 

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors