Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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.
@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
Thanks, but I don't see how that will help.
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.
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.
User | Count |
---|---|
132 | |
74 | |
70 | |
58 | |
54 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |