Forum Discussion
Dynamic value for Data Limit in the Table Visual
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.
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.
Hi Anonymous ,
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.
Thank you so much for your suggestion anilelmastasi
I will try it and let you know how I go.
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.