Images from CommCare to Power BI


This tutorial explains how images captured in CommCare can be shown on Power BI visuals with the help of data transformations on Power BI.

Step 1

Assuming that an app is already setup on CommCare with image files saved across one or multiple forms, set up the following oData feeds and exports:

  • OData feed for case data of case type being tracked.

  • OData feeds for all the forms that have an image question.

  • For each of these feeds, create a form export as well and export all the media to local storage.

Step 2

On Power BI, get data from the following sources:

  • OData for the feeds above

  • Folder for the folder locations where media is saved. As a best practice, save media in separate folders for the separate image captures.

Step 3

Rename queries to relevant names for the cases and form data. Go to transform data and open the advanced editor after choosing either of the folder queries where the images are stored. Edit the following in the code below and paste it.

  • Change folder source in line 2 as needed

  • Check the nomenclature of the filename in the Name column of the query and accordingly change the delimiters in the IDMatch line (line 28 below).

This code does two things:

  • It converts the media files into base64 image files that can be saved within the PBI database.

  • It creates an ID Match column to help us create a relationship between this query and the form/case feed queries from CommCare.

let //Get list of files in folder Source = Folder.Files("/path/to/files"), //Remove unnecessary columns RemoveOtherColumns = Table.SelectColumns(Source, {"Content", "Name"}), //Creates Splitter function SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000), //Converts table of files to list ListInput = Table.ToRows(RemoveOtherColumns), //Function to convert binary of photo to multiple //text values ConvertOneFile = (InputRow as list) => let BinaryIn = InputRow{0}, FileName = InputRow{1}, BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64), SplitUpText = SplitTextFunction(BinaryText), AddFileName = List.Transform(SplitUpText, each {FileName, _}) in AddFileName, //Loops over all photos and calls the above function ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)), //Combines lists together CombineLists = List.Combine(ConvertAllFiles), //Converts results to table ToTable = #table(type table [Name = text, Pic = text], CombineLists), // Add a column to extract text between delimiters AddIDMatchColumn = Table.AddColumn(ToTable, "ID Match", each Text.BetweenDelimiters([Name], "rr-form_", ".jpg")), // Adds index column to the output table AddIndexColumn = Table.AddIndexColumn(AddIDMatchColumn, "Index", 0, 1, Int64.Type) in AddIndexColumn

Step 4

Create relationships between the different queries:

  • Establish a 1 to 1 relationship between the case feed and the form feeds, mapping caseid to form case caseid.

  • Create a 1 to many relationship between the formid and ID Match columns in the queries with the images and their respective form queries.

See representative relationship map below.

Step 5

Navigate to the visualization section and follow these steps:

  • Create a measure on each of the image queries.

  • Use the following DAX for the measures:

Measure = IF( HASONEVALUE('Consent Form Signatures'[Name]), "data:image/jpeg;base64, " & CONCATENATEX( 'Consent Form Signatures', 'Consent Form Signatures'[Pic], , 'Consent Form Signatures'[Index], ASC))
  • Replace the table names with the relevant ones.

  • Save each measure (for example, measure_signatures and measure_photos).

Step 6

Choose the table visual and add the case name from the case feed and the two measures from the images queries as separate columns. If the previous steps have been successfully completed, the table should populate the images.

Step 7

Format visuals to create different reports - the table visual is the most stable one for showing images but has formatting limitations.

Step 8

Save the local pbix file and navigate to the PBI service online. Open the workspace where the semantic model needs to be saved and upload the saved pbix file. It is easier this way rather than publishing from PBI Desktop as it can lag without any error messages.

Sample Beneficiary level report generated from CommCare Data

Sample Table with all data points