Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now


Showcase: How Much Toilet Paper with What-If Parameters

The coronavirus pandemic is currently spreading around the globe. Two guys, Ben Sassoon and Sam Harris, have created a website 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 CO2 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 in Power BI Desktop using the What-If parameters for demonstration purposes.


How Much Toilet Paper screencast.gif


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:


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.