Using Visualization Slicer to Exclude/Filter Selection from Table

[Anonymous]

Power BI Community,

I am trying to figure out a way to accomplish the following:

Say I have a table that looks like this in Power BI: 

Along with a dropdown visualization slicer with the "Team" field containing the the four teams listed in the rows above.

Is there a way that I can select any one team in the slicer and it excludes them from the table?

But in a similar, separate drill down table, keep the filters to only show the selected team? (kind of like editing interactions and disabling the exclusion filter)

Ultimately, the goal is to have two tables with a selection slicer:

1) Total table representing team averages

2) Total table with team drill down to players

3) Select Team(s) slicer

This way I could select a team and compare it's performance (along with individuals) vs. the rest of the group

Thank you. 

[TomMartens]

Hey,

create a separate table that just contains one column with the teams, make sure that this table is not related to any other table, this is an unrelated table.

Team table

Now create a measure that flags each team, when the team is not IN VALUES('unrelated table'[team]), use this measre to filter the teams accordingly

Player table

Use the selected value from the slicer to filter for the team and show the players.

If you need more help, please prepare an Excel sheet with sample data, maybe with separate sheets for teams and players. Upload the xlsx to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

Thank you for your reply, Tom. 

Maybe I am not understanding correctly, but how do you mean create a measure that flags each team?

I was attempting to do it the following way but it doesn't seem to be working:

TeamFlag = IF(SELECTEDVALUE(Sport[Team]) NOT IN VALUES(Unrelated[Team]), 0, 1)

I first tried to use '<>' but then tried this method. It seems DAX does not have a strict NOT IN function.

Also, my current query has the team and player columns already in my existing table. Along with the unrelated team table, I should create an unrelated player table as well?

Provided are the sample pbix and xlsx file from dropbox.

Team PBI

Team.xlsx

Thank you for your help.

[TomMartens · Accepted solution]

Hey,

thanks for uploading sample data.

I created a simple "Team" table, by using this simple DAX statement:

Team = 
VALUES(Sheet1[Team]) 

The idea behind this table is just this - use a column from an unrelated table for the Team slicer.

I created the following measure that returns TRUE if something from the team table is selected:

chkFilteredTeam = 
ISFILTERED('Team'[Team]) 

I assigned this measure to the table Team, basically this measure helps if nothing is selected in the slicer to show all teams and all player.

I also created the following two measures

SelectedTeam = 
IF([chkFilteredTeam] 
    ,SUMX(
        'Sheet1' 
        ,IF('Sheet1'[Team] IN VALUES(Team[Team]),1,0)
    )
    ,0
)

SelectedTeamPlayer = 
IF([chkFilteredTeam] 
    ,SUMX(
        'Sheet1' 
        ,IF('Sheet1'[Team] IN VALUES(Team[Team]),1,0)
    )
    ,1
)

I then used the measure "SelectedTeam" in the visual level filter of the matrix visual that "just" shows the team:

and the 2nd measure in the visual level filter band of the 2nd matrix visual like so:

I guess the following screenshot shows what you are looking for, please be aware that the upper slicer is not used the lower slicer is based on the new table:

Regards,

Tom

[Anonymous]

Thank you for your help, Tom. That worked!

As a follow up, I am trying to do the same thing specifically for players (so when I select a player, it includes only their stats in a new, players only table but the averages of the rest in the existing second table). The problem I'm facing is that as I create a new unrelated table for players, it is also unrelated to the team table. As a result, if I have two slicers: Team and Player, when I select a team in the slicer filter, it does not filter my subsequent players slicer (to show only those players associated with the team). Is it possible to relate the players with the teams, while still continuing to maintain the selection?

Regards