cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Etienne123
Advocate II
Advocate II

Need help with DAX and visuals to highlight relationships between two values

Hi

I have a single table with Value1 and Value2 values, for example:

 

Value1 - Value2

A - B

B - C

B - D

C - E

 

The relationships do not apply in a particular direction, and visualising this using something like a Forced-Directed Chart shows the relationships quite well. You can see how it shows all the relationships with B, irrespective as to whether B is in Value1 or Value2.

 

Question1.JPG

 

But the data needs to be filtered by B and if I base the filter on Value1, then I will only see the relationships with C and D, and conversely, if I base the filter on Value2, then I will only see the reltionship with A. I need to be able to select B and show A, C and D.

 

I think what I need do is to ensure all distinct values across Value1 and Value2 and represented in (for example) Value1 and for all its relationships to appear in Value2. Something like

 

Value1 - Value2

A - B

B - A

B - C

B - D

C - B

C - E

D - B

E - C

 

Is this the solution, and could such a dataset be derived?

 

 

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @Etienne123 

 

The solution you have suggested is presumably the best, and it's easy to achieve both in Power Query and using DAX.

I've attached a file with both solutions, let me know if you need any explanation.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

Hi @Etienne123 

 

The solution you have suggested is presumably the best, and it's easy to achieve both in Power Query and using DAX.

I've attached a file with both solutions, let me know if you need any explanation.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

View solution in original post

Before I saw this, I ended up doing somethikng similar to the PQ soluition, but I really like the DAX UNION method.

 

Thanks!!

Mariusz
Community Champion
Community Champion

Hi @Etienne123 

 

I'm glad you like it, just a few things to pint out here, the best practice is to do your transformation is PQ.

  1. Most of the time your model gets better compression rates than when using DAX columns/tables.
  2. You can load only one result table ( saves the model size and removes unnecessary objects )

However, with smaller models, it shouldn't make much of a difference.

 

Thanks 

Mariusz

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors