• all participants have access to PCI Guatemala project space with admin rights.
  • all participants have Power BI installed on their machines


WE GAP PACE baseline was conducted using mobile data collection. We will use power bi to connect to the data directly from CommCare through a query.

Task 1. 

Connect power bi to WE GAP PACE data in CommCare (instructions can be found here)

CommCare excel dashboard link:


  • Understanding Query, Data, Report
  • understanding your data

let’s discuss the following simple question:

How many surveys were conducted?

How do we determine this? what constitutes a complete survey? where do we need to go to look for this data?

When we look at the paper tool what do we see?

Task 2.

Create a card that shows how many surveys have been completed

Now that we know what constitutes a complete survey we can build this “simple” visual so let us take a look at the data available


Do we have the fields required?

Do we need to create new columns and/or measures?


In query editor create conditional column

consent_over18 = 1, return 1 otherwise return 0

repeat the same for consent_under18

Now in data create a column that adds both consent to a single column thus creating a single column that tracks surveys completed

total count = consent_over18 + consent_under18

Now return to report and drag the card visual and select total.


I want to know how many surveys have been completed per day as well as a running tally of surveys

What do we need to know?


a sum of the total surveys filtered by time


create a visual that shows daily and total.. hmmm.. what type of visual would be great good for this?


sounds like a perfect use of the

line and stacked bar graph

what do we notice about the date and time?

in the query editor – modify date column to be date

in the data editor – modify the date to appropriate format

create a measure that sums total count with what’s selected between the minimum and max date of the date column (confusing i know)

running total = CALCULATE(
    SUM(PACE[total count]),
        ISONORAFTER(PACE[received_on], MAX(PACE[received_on]), DESC)
insert line and stacked bar graph with the following fields: running total, total, received on date)


What about maps?


create a map of the data

show me how

enable shape maps 

insert shape map visual and drag the location to the field

download json file at the bottom of this page

in the format section select custom shape and drag the .json file you downloaded.


note: data in the location field must match the data in the json field. what is not shown is the work required to create the json file.

Resource links

create a running total

a.count running total =

    SUM(PACE[total count),
        ISONORAFTER(PACE[received_on], MAX(PACE[received_on]), DESC)