Filter chart by selected user's group

[RobbLewz]

I have an individual employee report page I am putting together.

The majority of the information on the page will be for the choosen employee.  However, I have a bar chart that I want to show everyone in the same group as the selected employee but having trouble achieveing this.

I have a single select slicer where you can choose an employee by name.  The slicer is configured to filter most things on the page but the column chart is set to ignore the slicer, so when I choose an employee it displays all employees in their group.

table strucure is roughly like so

DimEmployee

Id	Name	Group
1	Mary	Group A
2	Paul	Group A
3	Lisa	Group B
4	Fred	Group B

FactTotals

EmployeeId	Total
1	1000
2	3000
etc	etc

So if I selected "Mary" from the slicer, the chart should show Mary & Paul as the axis and their SUM(total) as the values.

I can't use SELECTEDVALUE(DimEmployee[Name]) as Measure on the chart because it isn't being filtered by the filter so evalutes on a row basis.

Is there a way to lookup the SELECTEDVALUE() of a specific slicer or store it as a global parameter? 

I also tried creating a hidden slicer.  The slicer just showed the groups, and was filtered by employee slicer.  The problem is though, if you select Mary, this group slicer would highlight Group A but when you changed Selection to lisa, it adds group b to the list but keeps group A highlighted, so useless really.

[Anonymous · Accepted solution]

Hi @RobbLewz ,

According to my understand, you want to display all other users in the same group as the selected user, right?

Please follow these steps:

1. Create a new table with Name columns for slicer like this:

2. Build relationships based on IDs.

3. Create a measure using the following formula and apply it to filter pane:

Measure =
VAR _group =
    CALCULATE (
        MAX ( 'DimEmployee'[Group] ),
        FILTER (
            ALL ( 'DimEmployee' ),
            'DimEmployee'[Name] = SELECTEDVALUE ( ForSlicer[Name] )
        )
    )
RETURN
    IF ( MAX ( 'DimEmployee'[Group] ) = _group, 1, 0 )

 

Here is the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Eyelyn Qin

[RobbLewz]

Thanks for the reply.

Your method sounds like it will work, I found a very similar way in the end.  I duplicated the employee table (which also has the group id in it) for the slicer.  I joined the duplicated employee table to the "normal" employee table using a distinct list of group Id's as a bridging table.

If you use the "normal" employee table to provide the employee name as the chart axis then there is no need for a filter as that comes from the bridging table between the normal and duplicate table.

It's shocking sometimes how over complicatied everyting must be in power BI to achieve such simple things.