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 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.
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.