Forum Discussion

katemke's avatar
katemke
Frequent Visitor
6 years ago
Solved

Slicer Value from different table

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 NameInvestigator EmailYears of ExperienceAcademic Rank
Jane[email protected]10Professor
Tim[email protected]5Associate Professor
Kate[email protected]8Associate Professor
Nick[email protected]15Assistant Professor

 

Table 2

Team NameInvestigator EmailStatus on Team
Team 1[email protected]Active
Team 2[email protected]Not Active
Team 1[email protected]Not Active
Team 2[email protected]Active
Team 1[email protected]Active
Team 2[email protected]Active
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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