Forum Discussion

SurajManghani's avatar
SurajManghani
Regular Visitor
6 months ago
Solved

Limiting Rows in a Visual to 500k

Hi Community,

I have a requirement in which I have to count the number of rows in a metrics visual after applying all the filters and slicers. And the data should only be visible if the rows in the visual is below 500k. If the number of rows is greater than 500k no records should be visible in the metrics visual. 

Also I want to pop up a message in card visual to show to users that the number of records are more than 500k and they should change the slicer selections.

Please let me know if you done something similar to this or have any approach through which I can achieve this.

Need Help
  • danextian's avatar
    danextian
    6 months ago

    Hi SurajManghani 

     

    You cannot rely on counting the rows in a table because this may not reflect the actual number of rows shown in the visual. Instead, the DAX query used in the visual needs to be replicated.

     

    The table below uses this DAX formula to get the number of rows:

    Number of Rows in the visual = 
VAR _tbl =
    SUMMARIZECOLUMNS (
        'Category'[Category],
        'Category'[sort],
        'Dates'[Date],
        'Geo'[Geo],
        "Total_Revenue", [Total Revenue]
    )
RETURN
    COUNTROWS ( _tbl )

    Create this measure as warning text to be used in a card

    Warning = 
IF (
    [Number of Rows in the visual] > 1000,
    "More than 1,000 rows are visible. Use the slicers to reduce the number of rows so the data can be displayed.",
    "The table contains " & [Number of Rows in the visual] & " rows."
)

    Use this measure as a visual filter

    Visual Filter = 
VAR _limit = 1000
RETURN
    IF ( CALCULATE ( [Number of Rows in the visual], ALLSELECTED () ) <= _limit, 1 )

    Please see the attached pbix.

     

4 Replies

  • danextian's avatar
    danextian
    Super User
    6 months ago

    Hi SurajManghani 

     

    You cannot rely on counting the rows in a table because this may not reflect the actual number of rows shown in the visual. Instead, the DAX query used in the visual needs to be replicated.

     

    The table below uses this DAX formula to get the number of rows:

    Number of Rows in the visual = 
VAR _tbl =
    SUMMARIZECOLUMNS (
        'Category'[Category],
        'Category'[sort],
        'Dates'[Date],
        'Geo'[Geo],
        "Total_Revenue", [Total Revenue]
    )
RETURN
    COUNTROWS ( _tbl )

    Create this measure as warning text to be used in a card

    Warning = 
IF (
    [Number of Rows in the visual] > 1000,
    "More than 1,000 rows are visible. Use the slicers to reduce the number of rows so the data can be displayed.",
    "The table contains " & [Number of Rows in the visual] & " rows."
)

    Use this measure as a visual filter

    Visual Filter = 
VAR _limit = 1000
RETURN
    IF ( CALCULATE ( [Number of Rows in the visual], ALLSELECTED () ) <= _limit, 1 )

    Please see the attached pbix.

     

    • v-menakakota's avatar
      v-menakakota
      Community Support
      6 months ago

      Hi SurajManghani ,

      Thanks for reaching out to the Microsoft fabric community forum. 

       

      I would also take a moment to thank  danextian  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

       

      Best Regards, 
      Community Support Team 

       

      • v-menakakota's avatar
        v-menakakota
        Community Support
        6 months ago

        Hi SurajManghani ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

         

        Best Regards, 
        Community Support Team 

  • MattiaFratello's avatar
    MattiaFratello
    Super User
    6 months ago

    Hi SurajManghani,

     

    Create this DAX to count all your rows: 
    Row Count = COUNTROWS(ALLSELECTED('YourTable'))

     

    Create a 2nd DAX Measure to use as filter:
    Show Data = IF([Row Count] <= 500000, 1, BLANK())

    Drag Show Data to Filters pane > Visual level > Show Data is 1

     

    Create another DAX Measure as a conditional warning message to display in your card

    Warning Message = IF( [Row Count] > 500000, "Number of records >500k. Adjust slicers.", BLANK() )

     

     




    Proud to Be a SuperUser ✨
    If this helped, please mark as Answer and give kudos 👍