The coronavirus pandemic is currently spreading around the globe. Two guys, Ben Sassoon and Sam Harris, have created a website https://howmuchtoiletpaper.com/ which explains that you do not need to hoard toilet paper. The website calculates the amount you need to survive a quarantine without changing your habits. Cool stuff. But what does it have in common with Power BI? Let’s find out.
But what if you want to learn a new feature, you want to start from scratch, build something simple and also useful? I would like to recreate the calculation from the website https://howmuchtoiletpaper.com/ in Power BI Desktop using the What-If parameters for demonstration purposes.
How to create a What-If parameter
Navigate to the Modeling ribbon and click on the New parameter button.
A window pops up and you write down the name of the parameter and some other configuration values:
When you click on OK, Power BI Desktop creates a table called Days of quarantine with a calculated column and a measure.
The calculated column Days of quarantine has the following definition:
Days of quarantine = GENERATESERIES(1; 28; 1)
And the corresponding measure Days of quarantine Value returns the selected value or a default value if non selected:
Days of quarantine Value = SELECTEDVALUE('Days of quarantine'[Days of quarantine]; 14)
I have also created other What-If parameters in the same way.
The math behind the report
I will need two dead easy measures which use our What-If parameters:
Days to last: calculates the count of days which I have toilet paper for
Per cent of quarantine: how many per cent of a quarantine I can overstay
Days to last =
VAR sheetsYouHave = 'Rolls you have'[Rolls you have Value] * 'Sheets on roll'[Sheets on roll Value]
VAR dailyConsumption = 'Toilet visits per day'[Toilet visits per day Value] * 'Average number of wipes per trip'[Average number of wipes per trip Value] * 'Sheets per wipe'[Sheets per wipe Value] * 'People in household'[People in household Value]
RETURN sheetsYouHave / dailyConsumption
Per cent of quarantine =
[Days to last] / 'Days of quarantine'[Days of quarantine Value]