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