Create Chart to show information of N: N related entities

[rstbastos]

I have a requirement to create a Chart to show Orders and the count of Contacts associated with this Order (this is n:n relationship).
I've seen many examples using Collection to get the data, but all the cases have a lookup field to associate both entities.

How can I get this related information using Collection?
Or if there's another way to get this data, please share it.

Thanks in advance.

[rubin_boer]

hi @rstbastos it sounds like you can get away with a groupby function. AddColumns(GroupBy(DataSource,"Order","All Orders"),"# of Contacts",CountRows('All Orders'))

this will yield something like this:

is this what you looking for?

[rstbastos]

@rubin_boer your answer helped me achieve something that I was not being able before, but unfortunately it's not the way I wanted.
The way did:
   1 - first adding gallery to show the Orders.
   2 - then, when I select one order the chart is showed (I applied your code to the chart).
But in this way I'm showing the Contacts for one Order at a time.

I'm wondering if there's a way to create Collection(s) with all orders and the contacts related to those orders (remembering that this is n:n relationship).
Then the chart would show all Orders and the count of Contacts for each Order.

[rubin_boer]

hi @rstbastos can you make an excel example of what it is you have and wish to see.

[rstbastos]

So, let's say I have the Orders and Contacts below.

As it's n:n relationship, there's no lookup field in Order or Contact to relate each entity. This is a native n:n relationship, where the intersection table was created by Dynamics,  not by me. So I can't use the intersection table to associate the records. 

Order	Contact
Order 1	Contact 1
	Contact 2
Order 2	Contact 3
	Contact 4
	Contact 1
Order 3
Order 4	Contact 2
	Contact 6
	Contact 15
	Contact 17

I don't even know if this is possible, as I'm new in Power Apps Canvas, but my idea is to get to this, so that I can show this information in the Chart

Order	#Contacts
Order 1	2
Order 2	3
Order 3
Order 4	4