Show visual only if number of rows are below x

[mowmassimow]

Hi,

I have actually a fairly simple problem:

I want to show a visual (e.g. table) only if the amount of rows (->COUNT(TABLE[ID])) is below a certain number. If the number of rows is above this number, the table should not be shown/calculated.

I already created multiple different measures and added them to filters etc. but it always ends up the same way: Power BI tries to show the whole table (and then breaks because out of memory) before doing the check like described above.

Things that are not an option for us:

- Direct query

- Simplifying the visual, as there are just columns from one table without any measure or calculated table

Thanks in advance!

Best,
Moritz

[mowmassimow]

Hi @v-xianjtan-msft,

thanks for your answer.

COUNTROWS() might perform better in general, but it doesn't fix the core issue: Checking the condition (count less than X), before calculating the whole table.

Best,
Moritz

[v-xianjtan-msft]

Hi @mowmassimow 

Please try using COUNTROWS instead of COUNT.  

Use COUNTROWS instead of COUNT in DAX - DAX | Microsoft Learn

Use this measure as a filter condition for visual. Set the filter condition to ShowTable = 1.

ShowTable = IF(COUNTROWS(YourTable) < [certain number], 1, 0)

If out-of-memory errors still occur, consider optimizing the data model, such as using SQL queries or Power Query to limit data loading to only necessary rows of data.

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mowmassimow]

Hi @v-xianjtan-msft,

thanks for your answer.

COUNTROWS() might perform better in general, but it doesn't fix the core issue: Checking the condition (count less than X), before calculating the whole table.

Best,
Moritz

[v-xianjtan-msft]

Hi @mowmassimow 

What you said is correct. The logic for calculating this measure is to count the whole table then compare it to the certain number in the condition. Therefore, we recommend that you reduce the amount of data being processed or optimize your data model to avoid running out of memory. If you are using a virtual machine, try allocating more memory.

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mowmassimow]

Hi,

as I said, I can not optimize anything in the datamodel or reduce the amaount of data more.

How can it be so difficult to have one simple calculation (countrows), before starting with another calculation???

Best,
Moritz