Select value and show corresponding values associated within group of selected value in bar chart?

[user35131]

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. 

[smpa01]

@user35131  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.

[AllisonKennedy]

@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 

[user35131]

@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. 

[AllisonKennedy · Accepted solution]

@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]

Using Version 2.93.64 so i can't view the pbix folder.  

[user35131]

I did get it work. Thank you. This got the job done.

[AllisonKennedy]

Great! Glad it worked and you got the file working or Power BI updated. 🙂