Expericend Qlikview developer, but newbie PowerBI, any pointers please?

[RedSky001]

I’m trying to learn PowerBI and I figured the best way is to try and mimic something I understand (Qlikview).  In Qlikview I’ve a chart which shows the number of Covid-19 fatalities by country by day (where day 1 is 10 deaths).

(Similar graphs have been on new sites, so maybe someone has already done this in PowerBI here?)

Screen shot in this link: https://ibb.co/DMLtqYh

I’d appreciate, experienced PowerBI giving me some clues where to start and which tools they would recommend ? (Ie can all the data modelling be done in Power Query / DAX?)

Here is some more detail on the current graph:

The only data I’m using is from the John Hopkins (available here):

https://data.humdata.org/dataset/novel-coronavirus-2019-ncov-cases 

(A list of countries, dates and number of fatalities.)

Each line represents a country.  

The Y Axis is the number of fatalities.

The X Axis is the number of days since the first day the country had 10 fatalities (This is done because countries are at different stages of the epidemic and makes comparisons easier).

It also has two trend lines.  Showing the trajectory of fatalities if the figures double every two and three days.

The part which I’ll struggle with in the trend lines.

I did this in two parts. 

First create two new ‘countries’’ countries, called ‘Doubles2Day’ & ‘Doubles3Day’ and create values which grow expodtenailly.  This was done using For Next Loops.

Next create a new table which has in it Country Name & Days, where Days starts at 1 when that country and 10 or more fatalities,

14-March-2020 - UK - 1

15-March-2020 - UK - 2

16-March-2020 - UK - 3 

Etc

To create this table I had to get the Start and End Date for each country where the fatalities were greater than or equal than 10.  I then used a function called IntervalMatch to get all the dates in between.

So if anyone can give me some pointers (I'm not expecting a full soluiton), I would a really appreicate it. 

Mark

[v-lid-msft]

Hi @RedSky001 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @RedSky001 ,

We can try to create a calculated column and measure to meet your requirement.

Firstly, we need to change the construction of data as following:

Then create a calculated table as slicer and legend:

Country = UNION(DISTINCT('time_series_covid19_deaths_global'[Country/Region]),{"Doubles Every 2 Days","Doubles Every 3 Days"})

Create a calculated column in fact table:

Days (1 equal 10 Death) = 
var tempT = SUMMARIZE('time_series_covid19_deaths_global','time_series_covid19_deaths_global'[Date],'time_series_covid19_deaths_global'[Country/Region],"DayValue",SUM(time_series_covid19_deaths_global[Value]))
var minDate = MINX(FILTER(tempT,[Country/Region] = EARLIER('time_series_covid19_deaths_global'[Country/Region]) && [DayValue] >=10),[Date])
return 

IF(ISBLANK(minDate) || minDate> [Date],BLANK(),DATEDIFF(minDate,[Date],DAY)+1)

Then create a measure as value:

Measure = 
SUMX (
    DISTINCT ( 'Country'[Country/Region] ),
    SWITCH (
        [Country/Region],
        "Doubles Every 2 Days", 10
            * POWER (
                SQRT ( 2 ),
                MIN ( 'time_series_covid19_deaths_global'[Days (1 equal 10 Death)] ) 
            ),
        "Doubles Every 3 Days", 10
            * POWER (
                POWER( 2,1/3 ),
                MIN ( 'time_series_covid19_deaths_global'[Days (1 equal 10 Death)] ) 
            ),
        CALCULATE (
            SUM ( time_series_covid19_deaths_global[Value] ),
            'time_series_covid19_deaths_global'[Country/Region]
                IN DISTINCT ( 'Country'[Country/Region] )
        )
    )
)

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?

By the way, PBIX file as attached.

Best regards,