Create a Slicer using 2 columns from 2 different tables and Use both columns in the slicer hierarchy

[Deevo_]

Hi all, many thanks in advance.

I have used this post to complete 50% of what I need for my report. 

https://community.fabric.microsoft.com/t5/Desktop/Create-two-slicers-from-two-different-name-fields-...

I need help with DAX. Instead of having one column in my slicer from one table, I require 2 columns that have been unioned from 2 different tables because each table has a different meaning. The two columns will be used as a hierarchy in the slicer to allow the user to select either the top OR select the lower level values.

My progress so far:

Step 1: create the slicer using a union and summarize to bring in 2 columuns [Division] and [Branch]. This step works fine.

Slicer =

DISTINCT(

        UNION(SUMMARIZE('Teams', 'Teams'[Division], 'Teams'[Branch]),SUMMARIZE('Project_Codes', 'Project_Codes'[Division], 'Project_Codes'[Branch]))

            )

Step 2: I need help here. I can only get one column [Division] to work in the slicer. How to write DAX to allow the user to select the field [Division] and/or [Branch] from the page slicer??

Measure =

IF (

    OR (

        SELECTEDVALUE ( 'Slicer'[Division] ) = SELECTEDVALUE ( 'Teams'[Division] ),

        SELECTEDVALUE ( 'Slicer'[Division] ) = SELECTEDVALUE ( 'Project_Codes'[Division] )

    ),

    1,

    0

)

Many thanks!

[Deevo_ · Accepted solution]

Hi All, I managed to solve this myself by tinkering with different methods. But it all came down to just creating 2 separate measures; one for [Division] and one for [Branch]. Then I applied both measurs to the visual as a filter and set both the values = 1.

Division Measure =

IF (

    OR (

        SELECTEDVALUE ( 'Slicer'[Division] ) = SELECTEDVALUE ( 'Teams'[Division] ),

        SELECTEDVALUE ( 'Slicer'[Division] ) = SELECTEDVALUE ( 'Project_Codes'[Division] )

    ),

    1,

    0

)

Branch Measure =

IF (

    OR (

        SELECTEDVALUE ( 'Slicer'[Branch] ) = SELECTEDVALUE ( 'Teams'[Branch] ),

        SELECTEDVALUE ( 'Slicer'[Branch] ) = SELECTEDVALUE ( 'Project_Codes'[Branch] )

    ),

    1,

    0

)

Thanks for stopping by.

[Deevo_]

Hi lbendlin, I should have explained this differently. The [Division] and [Branch] fields from each of the two tables have two separate meanings. First being, the Leader of the Project (Lead Division and Lead Branch) and the Second being, the Division and Branches who do the work on the projects (multiple Division and Branches can work on a project).

I have 2 visuals and each use [Division] and [Branch] from two different tables, but i only want 1 slicer to affect both visuals.

1) the total number of projects (Lead Division and Lead Branch)

2) the total number of hours worked on a project (Contributing Division and Contributing Branch).

I hope this makes sense.

[lbendlin]

No need for DAX.  Add the two columns to the slicer in the right order. The hierarchy will automatically be created for you.