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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
user35131
Helper III
Helper III

Select 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

GroupCount
115
214
313
412
524
624
725
823
931
1032

 

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. 

1 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 

 

AllisonKennedy_4-1634360577069.png

 

 

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: 

AllisonKennedy_2-1634360528376.png

 

But if you use the DimNum filter, you'll only get records for that num = 8: 

 

AllisonKennedy_3-1634360564331.png

Let me know if anything doesn't make sense or doesn't work, and provide screenshots/detailed error messages. 

 

 


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

View solution in original post

7 REPLIES 7
smpa01
Super User
Super User

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

 

smpa01_0-1634305033132.png

smpa01_1-1634305056251.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
AllisonKennedy
Super User
Super User

@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 


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

@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 

 

AllisonKennedy_4-1634360577069.png

 

 

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: 

AllisonKennedy_2-1634360528376.png

 

But if you use the DimNum filter, you'll only get records for that num = 8: 

 

AllisonKennedy_3-1634360564331.png

Let me know if anything doesn't make sense or doesn't work, and provide screenshots/detailed error messages. 

 

 


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

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

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

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


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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.