Forum Discussion
Helper IIISelect value and show corresponding values associated within group of selected value in bar chart?
If i have two columns group and list, is there a way to select a value from list and show only list values that are associated with the group that the specific selection is associated with with a measure?
For example if i have a dataset that looks like this
Num | Group | Count |
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 1 | 2 |
| 5 | 2 | 4 |
| 6 | 2 | 4 |
| 7 | 2 | 5 |
| 8 | 2 | 3 |
| 9 | 3 | 1 |
| 10 | 3 | 2 |
So lets say i select 8, is there a way to get a bar graph that shows all the values associated with group 2 because 8 is associated with 2?
That would be 5,6,7 and 8. If i select 9 then I would get all values associated with group 3 because 9 is with group 3 so 9 and 10. The bar graph would show values 1 and 2.
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.
7 Replies
- AllisonKennedy
Community Champion
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
user35131AllisonKennedy 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.
- AllisonKennedy
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.
smpa01user35131 to be able to achieve this, you can create a derived table where you perform a self-join with the table itself to come to a calculated table. Once you have that, you can create a relationship between source and derived table and it will give you what you need. Pbix is attached.