Limiting Rows in a Visual to 500k

[SurajManghani]

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.

[danextian · Accepted solution]

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]

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]

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]

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 👍