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

     

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