Let us take up a scenario where we have customers associated with a store. The store generates a monthly reporting on what their total sales are, and, on the report, total sales metric is summarised at a customer level. Just imagine a table displaying all these customers with the total sales they generated for the store every month. Over time the number of rows in the table will keep on increasing.
The above scenario can create performance issues in Power BI when working with Big Data or where we have nearly million customers worth of data to display. From user point of view this can be quite challenging where users must wait to get the whole table loaded at first instance.
But, what-if there was a way, we can default the table to display only certain number of rows and let user control how many rows they want to see in the table visual.
From this point we will talk about What-If Parameters and how they can be seamlessly used to achieve this functionality. What-if parameters gives you a capability to drive your calculations using the values that the user inputs on the report page.
As I will not be going into the details on What-If parameters, kindly refer following article by RADACAD on this topic. https://radacad.com/power-bi-what-if-parameters
Let us take up a very simple example to illustrate the user control on number of rows to be displayed. In the image shown below I have got a simple dataset with just 2 columns. One is MONTH column and the other is the TOTAL SALES generated every month.
Let us now create a What-If parameter. To do this: Go to Modelling tab –> New Parameter as shown below:
Once you click on “New Parameter” option, following window appears:
Let us look at the different options on the window above:
- Name – Takes a name for the parameter.
- Data type – Whatever data type you want for your parameter. Available data types here are Whole Number, Decimal Number & Fixed decimal number.
- Minimum – Takes the lowest value for your parameter.
- Maximum – Takes the highest value for your parameter.
- Increment – Takes the value by which you want to increment your parameter.
- Default – Takes the default value of the parameter when no value is input in the parameter.
- Add slicer to this page – I prefer enabling this option as it by defaults creates the parameter as a slicer on the report page.
Let us fill up the values in the window for our example.
The maximum value I have taken here is 12, as we have total 12 calendar months. This value will be different in other scenarios where the volume of data is larger, so if there are 10,000 rows in the data then one can put maximum value as 10,000. I have also taken the default value as 1, because I want at least one row in my visual to get displayed when no user input is entered. This can also be defaulted to all rows.
The moment you hit OK in the above window; under the Fields Pane a table is created for the defined parameter.
This table has got a measure with the same name as the table. When you click on this table you see following DAX expression:
This is just generating a series with minimum and maximum value that we provided above for the parameter and incrementing it by 1. Details on GENERATESERIES can be found on official Microsoft’s document page: https://docs.microsoft.com/en-us/dax/generateseries-function
So far, the process was simple. From here starts the real challenge. How can we use this parameter in our source table to control what user wants to see on the visual? We just need to perform one more step and we will see the magic. The parameter by default is created as a slicer as shown below:
I want my parameter to accept minimum and maximum value. So, like the normal slicers in Power BI, I choose BETWEEN option to design it the way I am looking for.
Now our task is to drive our table visual by changing values in the parameter slicer. Just one more step away from our goal. Create the following measure in your main table with monthly sales:
- 1st line is the name of the measure.
- 2nd and 3rd line are about creating 2 variables taking the maximum and minimum values for the parameter as we want to give user capability to select a range.
- 5th line starts a SWITCH statement where I can pass multiple DAX expressions to be evaluated.
- 6th line I have mentioned to display everything when nothing is passed as a value in the parameter.
- Line 7th to 14th describe the main steps where we use the capability of parameters to control the visuals.
- 7th line Ranking is given to the rows in the data at a month level based on the total sales generated. RANKX details can be seen here: https://docs.microsoft.com/en-us/dax/rankx-function-dax
- 8th line I am using ALLSELECTED function here on my MONTH column because I want to get total sales at a Month level. (I have added Month Number also, as I have sorted MONTH column in data in chronological order using this column)
- 9th line I am taking a summarised total sale.
- 11th and 15th line I am comparing the ranking with the lower and higher values of the parameter. When a value is given the ranks are displayed in the range selected. (the statement returns TRUE against comparison)
So, this is the measure we need. Now let use see how this works. We add this calculated measure to the table visual as shown below along with the month column: (I have renamed the measure to “Sales” in the table visual)
We have Sales by Month. Now start changing the values in the parameter window and the table starts displaying the number of rows based on the range selected.
This is how we can give users control on volume of data they want to see on the report.
Hopefully, this article helps everyone out there! Don’t forget to leave your comments.
Pragati
