Dynamic value for Data Limit in the Table Visual

[SadiahKrasniqi]

Hello, 

I have built a PowerBI App with a Search Screen that has a combination of Slicer and Text Slicer visuals on the Dashboard with their “Apply Filters” button and numerous filters in the filter pane.

It then has a table visual which lists all results based on the slicers and filters set by the user.

The total number of records in the backend view/partitioned table is about 18million records.

The table visual has the data limit filter set to 100 which was so that results would be returned more quickly. However, the user wants to know if we can increase it to 500 and check if the performance is still OK.

I have set the Data Limit filter to Hidden because we don’t want the users altering the value themselves and causing performance issues.

What I would like to be able to do is set up a parameter and be able to modify its value under Semantic Model Settings, without having to redeploy the PowerBI application. Is it possible to use a paramter as a value in a Filter inside the Filter pane?

NOTE: Using the TopN or RankX options won’t work in this scenario as I need to reduce the number of returned records only AFTER all the slicer and filters have been applied, not before that. Also, because the slicing and filtering is based on a lot of the columns, creating a measure with ordering or ranking, I don’t think will work either unless it can be dynamically set based on the slicers and filters.

Thanks,

Sadiah

[anilelmastasi]

Hello @SadiahKrasniqi ,

How did you limit data? Can youı give more details please?

[SadiahKrasniqi]

Hi @anilelmastasi I am only limiting the result set by the data limit filter on the table visual which we initially set to 100, but planning to set to 500:

I do have to and from date field parameters but they are outer limits from like 2006 all the way up to 2025 and are the only checks in the SQL Where clause when retrieving the data from Data LakeHouse. For example:

let
Source = Sql.Database(#"SQL_DataSource", #"SQL_Database"),
SqlQuery = "SELECT * FROM [dbo].[GLD_INFRINGEMENT] WHERE 1=1
-- Date filters (always applied)
AND INC_DATE >= CONVERT(DATETIME, '" & DateTime.ToText(StartDateFrom, [Format="yyy-MM-dd HH:mm:ss"]) & "', 120)
AND INC_DATE <= CONVERT(DATETIME, '" & DateTime.ToText(StartDateTo, [Format="yyy-MM-dd HH:mm:ss"]) & "', 120)
",
QueryResult = Value.NativeQuery(Source, SqlQuery, [], [EnableFolding=true])
in
#"QueryResult"

The reason we can't limit the data returned in the initial call to the data is because the users will do several types of search that will have numerous results depending on what they are trying to look up.

In the search screen the to and from dates will be defaulted to 2020 and 2025 and the table visual is ordered by date descending so that the initial result list will only display the latest 100 records. 

As you can see by my first screen shot in the initial post, there are a lot of different fields that the user can filter on. 

I am going to "manually" change the value of the Table Visual's from 100 to 500 via the Edit option in the Fabric workspace so that more records are returned once the user chooses "Apply Filters". However it would be nice if there is a way I could instead use a parameter so that the limit could be changed instantly in the published app so that we can tweak it constantly online until we get the right balance between performance and what the users want to see.

[anilelmastasi]

Hi @SadiahKrasniqi ,

Power BI does NOT allow you to bind a parameter to a “Data Limit” filter inside the Filter Pane. Unfortunately, you cannot use a Parameter in the visual Filter Pane. This is a Power BI limitation. 

If I understand correctly can this method helpful?

Firstly you can create a parameter table like:

RecordsToShow = 100

RecordsToShow = 200

RecordsToShow = 500

RecordsToShow = 1000

Users will change it from a slicer.

And then you can add a row index to table. Like this, however you want:
RowIndex = RANKX(ALL('GLD_INFRINGEMENT'), 'GLD_INFRINGEMENT'[INC_DATE], , DESC)

After that you can create a mesaure:

ShowRow = IF( [RowIndex] <= SELECTEDVALUE(RecordsToShow[RecordsToShow Value]), 1, 0 )

And filter the table by this measure to 1.

[SadiahKrasniqi · Accepted solution]

Hi @anilelmastasi I have figured out the solution thanks to your advice 🙂

Initially I had a problem with the code: 
"RowIndex = RANKX(ALL('GLD_INFRINGEMENT'), 'GLD_INFRINGEMENT'[INC_DATE], , DESC)" 

because the ALL option was trying to calculate the value over the total number of records in the GLD_INFRINGEMENT(Infringement) table which is more than 20million and the RANKX measure is limited to calculate over 1million records max.

In summary:
1. I created the Parameter Table using: 

RowLimitSlicer =

DATATABLE (

    "RecordsToShow", STRING,

    "LimitValue", INTEGER,

    {

        { "100", 100 },

        { "200", 200 },

        { "500", 500 }

    }

)

2. I created 2 Measures in the Infringement table:

mRowIndex = RANKX(ALLSELECTED('Infringement'), CALCULATE(MAX('Infringement'[INC_DATE])), , DESC)

mShowRow = IF( [mRowIndex] <= SELECTEDVALUE(RowLimitSlicer[LimitValue]), 1, 0 )

3. I added the Single Select Slicer visual using RecordsToShow from RowLimitSlicer

4. In the result list Table Visual:

Added the fields, mShowRow, mRowIndex and Count of Inf_No (PK).

NOTE: The Count field is just there to confirm the right Row Limit is returned and not more.

Added a filter to the Table Visual that specifies mShowRow has to equal 1:

So the Dashboard page will now have:

I tested it using various options for the other slicers and filters and it works no matter what date range or values I enter 😊

Thanks again for your advice.

[SadiahKrasniqi]

Thank you so much for your suggestion @anilelmastasi 

I will try it and let you know how I go.