Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 months ago
Solved

Dynamic value for Data Limit in the Table Visual

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...
Need Help
  • Anonymous's avatar
    Anonymous
    8 months ago

    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.