Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Using Visualization Slicer to Exclude/Filter Selection from Table

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 visualiza...
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

     

     

     

     

     

     

     

     

TomMartens's avatar
TomMartens
Super User
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      TomMartens
      Super User
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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