Controlling Number of Rows to Be Displayed in a Table Visual Using What-If Parameters in Power BI

[Pragati11]

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, the 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 a user point of view, this can be quite challenging where users must wait to get the whole table loaded at the first instance.

But, what if there was a way, we can default the table to display only certain number of rows and then let the 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 the 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 the “New Parameter” option, the following window appears:

Let us look at the different options on the window above:

1. Name – Takes a name for the parameter.
2. Data type – Whatever data type you want for your parameter. Available data types here are Whole Number, Decimal Number and Fixed decimal number.
3. Minimum – Takes the lowest value for your parameter.
4. Maximum – Takes the highest value for your parameter.
5. Increment – Takes the value by which you want to increment your parameter.
6. Default – Takes the default value of the parameter when no value is input in the parameter.
7. Add slicer to this page – I prefer enabling this option as it, by default, 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 the 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:

1. 1^st line is the name of the measure.
2. 2^nd and 3^rd 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.
3. 5^th line starts a SWITCH statement, where I can pass multiple DAX expressions to be evaluated.
4. 6^th line I have mentioned to display everything when nothing is passed as a value in the parameter.
5. Line 7^th to 14^th describe the main steps where we use the capability of parameters to control the visuals.
   • 7^th 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
   • 8^th 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)
   • 9^th line I am taking a summarised total sale.
   • 11^th and 15^th 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:

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 the volume of data they want to see on the report.

Hopefully, this article helps everyone out there!

-Pragati

[Greg_Deckler]

This is awesome @Pragati11 , never thought about this before. Very novel! Am definitely adding this to my list of cool things to bring to my next Power BI project!

[Pragati11]

Hi @Greg_Deckler ,

I am glad that you find this article helpful. I myself have applied this to one of my projects where we are displaying a list of 2 million customers on a single tab. Introducing this functionality, has improved the performance of this tab and also user interactions.

Thanks,

Pragati

[AllisonKennedy]

@Pragati11 I like how you have used between, to enable us to ignore the top ranked values and focus on middle or bottom values too! 

[Pragati11]

Hi @AllisonKennedy 

Yes just tried to give a nice way of interaction to users. We can modify this based on the different requirement as well. 🙂

Thanks,

Pragati

[Kopek]

Hi @Pragati11 , that looks very useful! can you please tell me what exactly you have out to topN variables ?

Thanks!

[Pragati11]

HI @Kopek ,

I am not sure if I understand your question here 🙂

[Kopek]

Hi @Pragati11 , sorry, typing to fast!
Wanted to ask what exactly you have put into TopN variables, so Select Top and Select Bottom, cannot replicate it at my end 

[Pragati11]

HI @Kopek ,

TopN is a measure and it has been explained above in the article.

Can you tell me exactly what is not working at your end?

Measure clearly shows what SelectedTop and SelectedBottom is using.

[RajeshCh]

Hi @Pragati11 , when nothing selected, it displays all the data. what if its not able to show in the table due to huge data