At work we have a weight loss competition that was in full progress when I started there in June. After the summer vacation it’s time to start thinking about beach body 2020 and start again, but we received some scrutiny in the BI department that this was shown on a TV screen as an Excel sheet. Well, we can do better can’t we?

My solution has these components;
1. PowerApp for weight registration
2. Flow for sending data from PowerApp to SQL database and Power BI push dataset
3. Power BI dashboard with live updates

PowerApp

My PowerApp is a simple canvas app for mobile devices. This connects to my Azure SQL database to read in a table that has contestants. Apart from that it uses keeps track of the logged in user with the variable User().FullName, and fills the contestants with this formula: Distinct(DropColumns(‘[vektkonkurranse].[Vektdata]’, “RegistrertAv”, “Vekt”, “VektDato”), Deltager) (sorry for the Norwegian column names, but this drops all columns except Deltager and selects distinct values from this). When pressing register a Flow is triggered.

Flow

The flow is again quite simple. It triggers when the button is pressed in PowerApps and adds rows to the SQL database (always nice to have that as backup). In addition it sends data to a Power BI push dataset. Here we do a bit work to send in both the timestamp (use convertFromUtc to get your own time zone) and the date. There is a button to request parameters from PowerApp so that the run function for the button in PowerApp can send in variables from the form.

Power BI push dataset

One of the nice features of Power BI push datasets is that you can create measures in the dataset. This can only be done with the API and at dataset creation (and I haven’t found a way to update a dataset). My JSON for the dataset looks like this:

{
“name”: “VektKonkurranse”,
“defaultMode”: “Push”,
“tables”: [
{
“name”: “VektKonkurranseData”,
“columns”: [
{
“name”: “Deltager”,
“dataType”: “string”
},
{
“name”: “Vekt”,
“dataType”: “Double”
},
{
“name”: “RegistrertTid”,
“dataType”: “Datetime”
},
{
“name”: “RegistrertDato”,
“dataType”: “Datetime”
},
{
“name”: “RegistrertAv”,
“dataType”: “String”
}
],
“measures”: [
{
“name”: “StartDate”,
“expression”: “MIN(VektKonkurranseData[RegistrertTid])”
},
{
“name”: “StartVekt”,
“expression”: “LOOKUPVALUE(VektKonkurranseData[Vekt],VektKonkurranseData[RegistrertTid],[StartDate])”
},
{
“name”: “SluttDato”,
“expression”: “MAX(VektKonkurranseData[RegistrertTid])”
},
{
“name”: “SluttVekt”,
“expression”: “LOOKUPVALUE(VektKonkurranseData[Vekt],VektKonkurranseData[RegistrertTid],[SluttDato])”
},
{
“name”: “Vektendring (absolutt)”,
“expression”: “[SluttVekt]-[StartVekt]”
},
{
“name”: “Vektendring (prosent)”,
“expression”: “DIVIDE([SluttVekt]-[StartVekt],[StartVekt],0)”,
“formatString”: “Percent”
}
]
}
]
}

JSON for creating push dataset

Power BI report

Finally I created a Power BI report on the push dataset. Here I use the PowerApp custom visual to add my PowerApp, and by pinning tiles to a dashboard I get live updates. There is limited editing capacities for columns and measures for these datasets, so take som care in creating a good JSON for the push dataset.

So now it’s just to go ahead and exercise more and eat less…