Unanswered

Creating a pie chart with Dataverse

(0) Share

Report

Posted on by bhenggoh

Hi,

I am new to PowerApps and trying to create a simple Canvas app but hit a wall. I am hoping if someone can help point me in the right direction.

As shown above, there are two Dataverse tables (Table1 and Table2). Table2 is a list of locations which is used as a lookup for the "Location" column in Table1.

I wanted to create a pie chart based on the number of times the list of locations are visited (ie, count the number of times a location appears in Table1).

I have tried many methods, including Calculated Column, Collections, CountIf, AddColumns, but have hit a wall at every turn.

The latest code I have tried (I put this in the OnSelect for the "Update Chart" button):

ClearCollect(loclist,Table2S.Name);
Collect(loclist1,AddColumns(loclist,"LocationCount",CountIf(Table1S,Location=loclist));

My simple idea was to create a collection with two columns and then use this collection to populate the pie chart. The 2 columns in the collection is:

Column 1 -- list of locations (as per Table2)

Column 2 -- the number of times the location is visited (using countif on Table1).

The above codes doesn't work. Perhaps there is an easier solution to this?

Hope that the gurus here can help.

Thanks.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Mira Ghaly 11,415 Moderator on at

Like (2)

Report
Copy link

Link copied!
@bhenggoh

I have tried to reproduce at my end

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

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:

This is how my table looks:

Was this reply helpful? Yes No
Mira Ghaly 11,415 Moderator on at

Like (1)

Report
Copy link

Link copied!

@bhenggoh Has the solution worker for you?

Was this reply helpful? Yes No
bhenggoh 4 on at

Like (0)

Report
Copy link

Link copied!

Hi Mira

Thank you for your response. So sorry for the late reply. I was sick the last week.
I have created a new screen and two new tables (location and visits)

I added your formulae into the Onselect for the button I created.

But I got many errors. Being new to PowerApps, I have no idea what all these errors meant. Maybe I have done something wrong.

Was this reply helpful? Yes No