• Marek Vavrovic

Covid-19, Power Bi DAX measures

Dataset source: https://www.worldometers.info/coronavirus/

Data on the server: I have been using couple of tables, this is the main one.

I have created a view to display the values for the last update and the previous day (new cases – previous day new cases) but in the most cases I used DAX to create the measures in the Power BI.


Recovery index measure:

1: Recoveries Total =

CALCULATE (

SUM ( DimCovid[Recoveries] ),

FILTER ( DimCovid, DimCovid[Date] = MAX ( DimCovid[Date] ))

)

2: Cases Total =

CALCULATE (

SUM ( DimCovid[CasesTotal] ),

FILTER ( DimCovid, DimCovid[Date] = MAX ( DimCovid[Date] ))

)

3: RecoveryRate =

DIVIDE ( [Recoveries Total], [Cases Total], 0)


Let’s see how these measures got created.

7-day median/100K


1:

Population = CALCULATE ( SUM ( WorldometersOnline[Population] ) )

2:

PopRate = [Population]/100000

3:

7-day median/100K =

VARmed =

CONVERT (

CALCULATE (

MEDIAN ( VwDimCovid[NewCases] ),

DATESINPERIOD ( VwDimCovid[Date], LASTDATE ( VwDimCovid[Date] ), -7, DAY)

),

INTEGER

)

RETURN

DIVIDE (med, [PopRate], 0)


Daily limit/100K

Safe case threshold according the EU regulation is <25/14 days. Per 1 day is 25/14=1.786


Limit =

VAR lim =

DIVIDE ( [7-day median/100K], 1.786, 0)

RETURN

IF (

lim > 1,

FORMAT (lim, "0.00") & "x over limit",

IF (

lim = 1,

FORMAT (lim, "0.00") & "x =limit",

FORMAT (lim, "0.00") & "x under limit"

)

)


RN- reproductive number

New case over previous cases during some time period (7 days)


1: New Cases Last 7 Days =

CALCULATE (

SUM ( 'VWDimCovid'[NewCases] ),

DATESINPERIOD ( 'VWDimCovid'[Date], MAX ( vWDimCovid[date] ), -7, DAY )

)


2:

New Cases 8th-14th Day =

CALCULATE (

SUM ( 'VWDimCovid'[NewCases] ),

DATESINPERIOD ( 'VWDimCovid'[Date], LASTDATE ( vWDimCovid[date] ) - 7, -7, DAY)

)

3:

RN = DIVIDE([New Cases Last 7 Days],[New Cases 8th-14th Day],0)


Progress to zero

Corresponds to the percentage decline from a previously recorded peak level. The metric ranges from 0% (representing a geography that has not yet peaked) to 100% (representing a geography wherein 0 cases have been recorded for at least seven days)


1:

World7-day/avg Newcase =

CALCULATE (

AVERAGE ( vWDimCovid[NewCases] ),

DATESINPERIOD ( vWDimCovid[date], LASTDATE ( vWDimCovid[date] ), -7, DAY)

)

2:

AverageIncedenceMAXX =

MAXX (

SUMMARIZE (

vWDimCovid,

vWDimCovid[date],

"XNC", AVERAGE ( vWDimCovid[NewCases] )

),

[World7-day/avg Newcase]

)

3:

Progress to zero =

1 - ( DIVIDE ( [World7-day/avg Newcase], [AverageIncedenceMAXX], 0 ) )


progress to zero in the tooltip


Spread frequency

I was a little bit lazy to figure out average for my dataset, so I did it old statistic way…


1: InfectionIncidence =

VARday1 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ))

)

VARday2 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ) - 1)

)

VARday3 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ) - 2)

)

VARday4 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ) - 3)

)

VARday5 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ) - 4)

)

VARday6 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ) - 5)

)

VARday7 =

CALCULATE (

SUM ( VWDimCovid[NewCases] ),

FILTER ( vWDimCovid, VWDimCovid[date] = MAX ( VWDimCovid[date] ) - 6)

)

RETURN

(day1 + day2 + day3 + day4 + day5 + day6 + day7) / 7


{86400 = number of seconds in 1 day}


2:

String Duration Incedence Trend in Days Hours Minutes and Seconds =

VARvSeconds =

INT (DIVIDE (86400, [InfectionIncidence] ))

VARvMinutes =

INT (vSeconds / 60)

VARvRemainingSeconds =

MOD (vSeconds, 60)

VARvHours =

INT (vMinutes / 60)

VARvRemainingMinutes =

MOD (vMinutes, 60)

VARvDays =

INT (vHours / 24)

VARvRemainingHours =

MOD (vHours, 24)

RETURN

vDays & " Days , " & vRemainingHours & " Hrs : " & vRemainingMinutes & " Min : " & vRemainingSeconds & " Sec"


measure in the tooltip


Region / District: New cases in the last 14 days/100K

If no district is filtered the chart displays data for the region, if district is filtered the chart will show the data for a specific district.

1:

2-Week/NewCases =

CALCULATE (

SUM ( DistrictGeoData[NewCases] ),

DATESINPERIOD ( DistrictGeoData[Date], MAX ( DistrictGeoData[Date] ), -14, DAY)

)

2:

NI14-day Incid/100K =

IF (

HASONEVALUE ( DistrictGeoData[District] ),

CALCULATE (DIVIDE ( [2-Week/NewCases], DistrictGeoData[DistrictPopRate] )),

CALCULATE (

DIVIDE ( [2-Week/NewCases], DistrictGeoData[RegionPopRate] ),

DistrictGeoData[Region] = "Nitriansky"

)

)


Change (KPI in the red frame) Indicates the change from the previous day.

You need a dataset with consecutive dates otherwise this code won't be working correctly.

1:

Daily Confirmed =

CALCULATE (

SUM ( SlovakiaDetails[DailyConfirmed] ),

FILTER (

SlovakiaDetails,

SlovakiaDetails[Date] = MAX ( SlovakiaDetails[Date] )

)

)

2:

Daily Confirmed day before =

CALCULATE (

SUM ( SlovakiaDetails[DailyConfirmed] ),

FILTER (

SlovakiaDetails,

SlovakiaDetails[Date]

= MAX ( SlovakiaDetails[Date] ) - 1

)

)

3:

Daily confirmed change =

[Daily Confirmed] - [Daily Confirmed day before]

4:

DailyConfComment =

VAR Down =

UNICHAR (10134)

VAR Up =

UNICHAR (119108)

VAR Side =

UNICHAR (129094)

RETURN

IF (

[Daily confirmed change] > 0,

"(" & FORMAT ( [Daily confirmed change], "+#,##0") & ")",

IF (

[Daily confirmed change] < 0,

"(" & FORMAT ( [Daily confirmed change], "#,##0") & ")",

Side & " "

& FORMAT ( [Daily confirmed change], "#,##0")

)

)


Slovakia herd immunity

I used 2 open sources data from GitHub (.csv) to create this visual.

I took dates from the calendar table

calculating daily new cases from PCR and AG table

1:

PCRDennePrirastky =

CALCULATE (

SUM ( PositiveTests_District[DennychPrirastkov] ),

FILTER ( 'Calendar', 'Calendar'[Date] = MAX ( PositiveTests_District[Datum] ))

)

2:

AG = CALCULATE(SUM(AgTests_District[Ag_Pos]))

I attached the measures to the table


Calculating running totals for PCR and AG test results. I created a new column for AG tests (1.8 * AG) because AG tests can only detect 50-55% of what PCR tests would detect in a similar circumstances. Don't worry about this statement.

3:

Ag_spolu =

CALCULATE (

SUM ( [50%AGplus] ),

FILTER ( Promorovanost, EARLIER ( Promorovanost[Date] ) >= Promorovanost[Date] )

)

4:

PCR_spolu =

CALCULATE (

SUM ( [PCR] ),

FILTER ( Promorovanost, EARLIER ( Promorovanost[Date] ) >= Promorovanost[Date] )

)

5: (sum of values from PCR and AG running totals )


AG_PCR_Spolu = [AG_spolu]+[PCR_spolu]


6: (Calculating the herd imunity)


%_Premorovanost =

DIVIDE ( [AG_PCR_Spolu], [SR_population], 0)


Newly created table for the visual


37 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn