Here’s Sue’s entry for Power BI Challenge 11. @Sue, feel free to add other details of your work.
Here’s the link to the report:
Here’s how Sue described it:
Step 1 – created a query to pull in the first data set. Created a parameter for a filename. Then changed the filename in the query to the parameter filename, and ensured that the query would dynamically pull in all the columns as the data is added to.
Step 2 - I then created a list of the different sources that I wanted to pull in and expanded the first query for each file name. I’ve commented the steps in Power Query code below and attached a copy of the .pbix file. This is a useful example to follow from Guy in A Cube in how to do the custom function part.
You end up with a table like this.
I then created a dimension table of countries and Region with an index:
Then I wanted to be able to link my fact table with my country table so created a merge and pulled in the Index to the fact table.
This was a fairly iterative process which I refined over time to ensure my model worked.
Once loaded into the data model, I created a date table using CalendarAuto in DAX:
I then started to create my measures. The numbers are cumulative so rather than using sum for my first measure I used Max to get my total. I could then use start to use calculate to Filter by Confirmed, Recovered and Deaths.
To use the visual bars only in the conditional formatting, I used exactly the same measures but called them a different name, eg Total Confirmed M.
I wanted to make the visual as simple as possible so had seen a video about ranking by sqlbi.com1 so used that as a basis to write some ranking measures. I would find it difficult to explain it in as clear way so definitely worth watching.
I created a table called TopN (TopN = GENERATESERIES(0, 200, 1)which generated a series of numbers from 1 to 200. There are 193 different country entries.
The main difference in my code, compared to the example, is that I was using Max not Sum, so had to change a few items in the Total w’Others measure.
My visuals are all fairly simple, I used a Ranking measure to sort the matrix so that Others would be at the end of the table irrespective of number of countries chosen.
Then to stop the Ranking table being visible I used the Field formatting to hide the field by giving it the same colour as the background.
The background for the page I used PowerPoint and saw something that @alexdupler showcased on twitter. Hope that helps, sorry it’s not more comprehensive.
Any questions, happy to help,