Forum Discussion
Select value and show corresponding values associated within group of selected value in bar chart?
user35131 I highly recommend using Dimension tables, so your model should look like this:
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
https://excelwithallison.blogspot.com/search?q=it%27s+complicated
The screenshot above I'm suggesting for your model is a little unorthodox, but so is what you're asking for.
To make this model, you will need to create a DimTable (or grab it direct from data source if possible). I created the one in the attached in Power Query: Right click on your one table > Reference. Select only the Num, Group (and any other columns directly related to this dimension). You can use the Ctrl key to select multiple columns. Then Right click on one of the selected columns > Remove other Columns. Finally, ensure there are no duplicates on the lowest level of granularity (in the example data you provided that's Num). See my links above for Unique key descriptions if needed.
Then, as smpa01 has suggested, we do a self-join, but unlike smpa01 's example, my solution only requires doing the self join on the Dimension table. To do this, Duplicate the DimNum table in Power Query (Right click on the DimNum table > Reference). Rename it DimNum_Associated.
This method avoids any duplication of the Fact table, so keeps your model more efficient and data model size smaller.
Result is:
But if you use the DimNum filter, you'll only get records for that num = 8:
Let me know if anything doesn't make sense or doesn't work, and provide screenshots/detailed error messages.
user35131 This can be done - but is much easier if you have star schema data model. Can you provide more detail on your relationships and data model? Do you already have Dimension tables or is all your data only in one big table still? https://www.youtube.com/watch?v=n3Hp-eNqudw&list=PLypkKakuPceAP9uaG1uc305p_UGeXKUvM&index=1&t=53s
AllisonKennedy It's in one big table with no relationships. Very sweet dashboard i noticed when i select gold, silver, or bronze it does what i seek to do with mine. I have a duplicate table of the original as well.
user35131 I highly recommend using Dimension tables, so your model should look like this:
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
https://excelwithallison.blogspot.com/search?q=it%27s+complicated
The screenshot above I'm suggesting for your model is a little unorthodox, but so is what you're asking for.
To make this model, you will need to create a DimTable (or grab it direct from data source if possible). I created the one in the attached in Power Query: Right click on your one table > Reference. Select only the Num, Group (and any other columns directly related to this dimension). You can use the Ctrl key to select multiple columns. Then Right click on one of the selected columns > Remove other Columns. Finally, ensure there are no duplicates on the lowest level of granularity (in the example data you provided that's Num). See my links above for Unique key descriptions if needed.
Then, as smpa01 has suggested, we do a self-join, but unlike smpa01 's example, my solution only requires doing the self join on the Dimension table. To do this, Duplicate the DimNum table in Power Query (Right click on the DimNum table > Reference). Rename it DimNum_Associated.
This method avoids any duplication of the Fact table, so keeps your model more efficient and data model size smaller.
Result is:
But if you use the DimNum filter, you'll only get records for that num = 8:
Let me know if anything doesn't make sense or doesn't work, and provide screenshots/detailed error messages.
Using Version 2.93.64 so i can't view the pbix folder.
