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=””]
Countries = SUMMARIZE(CovidCases,
CovidCases[Country/Region],
CovidCases[Province/State],
“Case Count” ,count(CovidCases[Cases])
)
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