Forum Discussion

Deevo_'s avatar
Deevo_
Resolver I
2 years ago
Solved

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

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-then-sync-them/m-p/3786264#M1226162

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!

Need Help
  • Deevo_'s avatar
    Deevo_
    2 years ago

    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.

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • Deevo_'s avatar
    Deevo_
    Resolver I
    2 years ago

    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.

  • Deevo_'s avatar
    Deevo_
    Resolver I
    2 years ago

    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.