Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
gurujeet1
Frequent Visitor

Drill through with an active and an inactive relationship

I'm working on a games database where there are players and games. Each game has a player with the black pieces and a player with the white pieces. There is a Player table listing each player with a PlayerID, and a Games table that has a field for PlayerID_Black and PlayerID_White. The Player table has an active relationship with PlayerID -> PlayerID_Black and an inactive relationship with PlayerID -> PlayerID_White.

 

I do a drill through from a page listing all Player's to a detail page on a single Player. It uses the active relationship so it only sees the Game's where the Player had the black pieces. What is the best way to get the detail page to be able to see all the Game's for the player whether as black or white?

 

Thanks for any help.

1 ACCEPTED SOLUTION

I was able to solve my problem by restructuring the Game table in the Query Editor. Instead of one row per game with a Player ID for black and another for white, I created two rows per Game, with an additional Player ID field. In one row it holds the White Player ID and in the other row it holds the Black Player ID. The drillthrough now goes to this new ID field and see all Game's for a Player, not just the ones on the active relationship.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@gurujeet1 , not sure this can help, But in may release there is an condition drill through

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-2020-feature-summary/#_Cond_dest_drill

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks, but I don't see how that will help.

AllisonKennedy
Super User
Super User

Not sure I understand the problem, but I think you just need to create another visual on the detail page that uses a measure as the value. That measure will need to use the DAX USERELATIONSHIP function to display the inactive related fields.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thanks, but I don't see how creating a measure with USERELATIONSHIP helps. I'm trying to create a visual that shows all Game's where the Player drilled through to was playing as either black OR white. I can only see the Game's played as Black due to the drill through page having been filtered on the active relationship.

 

I do use USERELATIONSHIP elsewhere on the page in measures that allow calculation of number of wins a player has, by calculating the number of wins as black and then using USERELATIONSHIP to calculate the number of wins as white and then adding the measures together to get total wins, but that doesn't help with the Game's list visual. 

Game's games. No need to capitalize or use an apostrophe.

@gurujeet1
Thanks for the detailed reply. Are you able to share a sample file maybe through OneDrive? If you are able to get total wins then you should be able to apply the same strategy to get total games, and you should be able to use this to view all games.

What does the start page look like? You may need to turn off the 'keep all filters' option so that only the player is added as a filter and the games are not added as a filter on the drillthrough page, but from your original post I can't tell which visuals you're using and which are raw data tables, so screenshots or sample data will help.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

I was able to solve my problem by restructuring the Game table in the Query Editor. Instead of one row per game with a Player ID for black and another for white, I created two rows per Game, with an additional Player ID field. In one row it holds the White Player ID and in the other row it holds the Black Player ID. The drillthrough now goes to this new ID field and see all Game's for a Player, not just the ones on the active relationship.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.