Slicer Value from different table

[katemke]

I have two tables, one listing each investigator individually, and their years of experience. The second table lists each research team, and the status of the investigator on that team (active or inactive). Investigators can be active memebrs in more than one team. I have a define relationship between these tables linked by the investigator's email (the consistent value shared between each).

I have a report built that shows data about the all the investigators who are participating - average years of experience, count of each with a given academic rank, etc...

I want to be able to add a slicer to show this same data based by team for only investigators active on that. Adding the team name column to the slicer does not return the expected data. How can i link these two tables to create this slicer?

The tables are set up:

Table 1

Investigator Name	Investigator Email	Years of Experience	Academic Rank
Jane	jane@college.edu	10	Professor
Tim	tim@college.edu	5	Associate Professor
Kate	kate@college.edu	8	Associate Professor
Nick	nick@college.edu	15	Assistant Professor

Table 2

Team Name	Investigator Email	Status on Team
Team 1	nick@college.edu	Active
Team 2	nick@college.edu	Not Active
Team 1	kate@college.edu	Not Active
Team 2	Kate@college.edu	Active
Team 1	jane@college.edu	Active
Team 2	jane@college.edu	Active

[v-yiruan-msft · Accepted solution]

Hi @katemke ,

Please create a measure as below to get the average year of experience of active investigators on team:

Average year of experience = 
VAR _selTeam =
    ALLSELECTED ( 'Table 2'[Team Name] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table 1'[Years of Experience] ),
        FILTER (
            'Table 2',
            'Table 2'[Team Name] IN _selTeam
                && 'Table 2'[Status on Team] = "Active"
        )
    )

If the above one is not what you want, please provide your expected result and explain the details with examples. Thank you.

Best Regards

Rena

[FrankAT]

Hi @katemke ,

you can build a table which holds all emails addresses distinct. The model would look like (see figure):

Model view

The sample report uses the emails from table EMails in the slicer (see figure):

Sample reportRegards FrankAT

[Greg_Deckler]

@katemke - One possible way would be to filter your visual by "Active". You might need to do something along the lines of a complex selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.