How to Model Power BI Covid 19 Data

How to Model Covid-19 Data using Power BI

This is the second in my series on helping you turn the Covid data on GitHub into information.  In this post, I show you step by step on how to model Power BI Covid data using Power BI, Power Query and DAX formulas.  If you are new to Power BI or just interested in how to use Power BI to build your own Covid report this guide was designed for you.

Getting up to Speed – Model Power BI Covid

In the last guide (link below) we took GitHub data and transformed it into a model showing the cumulative Covid-19 cases.

In today’s guide we will be enhancing the model to make it easier to use.  Let’s get started and Model Power BI Covid data.

Build Power BI Covide Reports from GitHub Data

Completed Model Power BI Covid Example

This is the finished version of how to Model Power BI Covid, by the end you will have a country and date table showing the relationships between them.

Renaming

The first step is renaming our current table to “CovidCases” to make writing our DAX code easier in the future.

Create a New Table using DAX

To begin to Model Power BI Covid data.  In the Home tab under the Calculations section select New Table to open a new table in the DAX Editor.

Making a Countries Table

To create a countries table make sure to click the down arrow on the right to expand the editor.

Then delete the “tables =”and replace with our code below.

[/fusion_text][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Create a Date Table

We now want to create a data table ,therefore repeat the step above except we will be inserting a new code, found below, into the DAX Editor

[/fusion_text][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Date = CALENDAR(min(‘CovidCases'[Date]),MAX(‘CovidCases'[Date]))

Creating a Unique Id

To better utilize the data that we have we will be creating a Unique ID for each country/state combination.

In the CovidCases table we want to add a New Column which is the right most option in the Column Tools and insert this DAX code

We want to do the something with the countries table and our code for that is

Now we want to create a relationship between the two new columns we created by dragging the the Covid Key Colums onto each other while in the Model view.

Hiding Columns

In this step we are hiding our Date and Country/Region feilds in the Covid Cases table by right clicking and choosing hide in the drop down menu.

Completed Sample from Model Power BI Covid Guide

This is the initial report we get from the visual model filtered between China,Italy,and the US

This is the iniatial report we get from the visual model filtered between China,Italy,and the US

Indexing

We now want to index the countries and dates. This is done by adding a new column to the CovidCases column ,then we add some more DAX code replacing “table =” as usual

Index =

RANKX (

FILTER (

CovidCases,

EARLIER (CovidCases[CovidKey] ) = CovidCases[CovidKey]

),

CovidCases[Date],

,

ASC

)

Calculating New Cases

The last peice of DAX code we will be using is a formula to find the amount of new covid cases daily.

New Cases =

VAR myindex = CovidCases[index]

VAR mycustomer = CovidCases[CovidKey]

VAR previousindex =

CALCULATE (

MAX ( CovidCases[index] ),

FILTER ( CovidCases, CovidCases[CovidKey] = mycustomer && CovidCases[index] < myindex )

)

VAR previouscases =

CALCULATE (

SUM ( CovidCases[Cases] ),

FILTER (

CovidCases,

CovidCases[index] = previousindex

&& CovidCases[CovidKey] = mycustomer

)

)

RETURN

IF ( previouscases >= 0 , CovidCases[Cases] – previouscases )

Final Reports

You can now use Power BI’s Reporting tool to make all kinds of data visualisations.In this example we made the amount of world cases vs. the amount of cases in the US.

CDO Advisors Microsoft Power BI Solutions:

BI as a Service – Low Monthly Fees, Pay Per Month Per User

Executive Dashboards – Get Dashboards in 4 Weeks

Healthcare Dashboard Proof of Concept – SEE your data visualized

Microsoft Power BI Demo – Compare your current reporting to Power BI

Power BI Quick Start Solutions – Ready to start or enhance your Power BI?

Train Your Team – Remote Power BI Developer and End User Training

Use Power BI for Finance Teams – Learn how Power BI can improve operations

Virtual Chief Data Officer – Confused by all your data?  We can help

CDO Advisors YouTube – Subscribe and Watch Our YouTube Channel