Showcase: How Much Toilet Paper with What-If Parameters

[Nolock]

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.

I have never found a use case in the real world in which I could use the What-If parameters in Power BI Desktop. There are some on the Microsoft website like CO₂ Emission Analysis for Automotive Industry (the report is in English). And I have also seen a good one about Bitcoins from Elias Steinle.

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]

That’s all, no rocket science 😉. You can find the PBIX file on my GitHub: https://github.com/nolockcz/PowerPlatform/tree/master/Showcase%20How%20Much%20Toilet%20Paper%20with%...

Final note

I hope I was able to show you with this simple example how you can use What-If parameters in Power BI Desktop. And I would like to thank Ben and Sam for the idea.