Dataverse Pie Charts in Canvas app

So today I got a question on the PowerApps community on how to display a Pie chart based on DataVerse tables.

So the Scenario is as below, Locations Table and Visits where location is a lookup and on the pie chart we need to display the number of Visits per Location, I thought initially this should be easy the same as it should be on model driven app, so added the pie chart on on the Items I thought it would be as easy as linking it to visits and use the location column in the series but this did not work as expected as the Lookup (Location) was not showing up so I ended up by doing the solution described below, so this is the steps to create the data and do the pie chart!

Let’s Go!

1. I have created a Table called Location Primary column Name is Location Name

2. Visits Table where location is a lookup

To be able to get the pie chart working I had to do a Join using Add Columns , then Group BY , Then Add Columns again to get the row counts, this is the final expression:

AddColumns(GroupBy(ShowColumns(AddColumns(Visits,"LocationName",LookUp(Locations,'Location Name'=Visits[@Location].'Location Name').'Location Name'),"LocationName"),"LocationName","myGroup"),"count",CountRows(myGroup))

Let me explain part by part the above Expression:

No. 1 the Join: So We join the table visits with the table Locations and show the new Column in the Location Name

ShowColumns(AddColumns(Visits,"LocationName",LookUp(Locations,'Location Name'=Visits[@Location].'Location Name').'Location Name'),"LocationName")

No. 2 Add Grouping by Location and the location group name is myGrouping

GroupBy(ShowColumns(AddColumns(Visits,"LocationName",LookUp(Locations,'Location Name'=Visits[@Location].'Location Name').'Location Name'),"LocationName"),"LocationName","myGroup")

No. 3 Then add another column to count the grouping

AddColumns(GroupBy(ShowColumns(AddColumns(Visits,"LocationName",LookUp(Locations,'Location Name'=Visits[@Location].'Location Name').'Location Name'),"LocationName"),"LocationName","myGroup"),"count",CountRows(myGroup))

This yield the below collection as below: