Calculate the total rows, Ignoring Filters In Power BI

Scenario

In Power BI, I have a “Projects” table as SQL data source, I would like to show

• The total number of rows, ignoring the filter.
• The total number of rows, depending on the filter.

As below shown,

• The Blue bar indicates to the fixed rows count (Count Rows ALL).
• The Green indicates to the rows count (Count).

Add “Count Rows All” Measure In Power BI

This measure returns the Rows count, ignoring any filters.

• In Home tab, Add a new Measure.

• Set the Measure name and type the below formula

[code language=”HTML”]

ProjectFixedCount = COUNTROWS(ALL(‘Table Name’))

[/code]

The above formula counts all rows of the table that retrieved from ALL function.

All() function In Power BI

Return all the rows in a table by clearing and ignoring any filters that might be applied.

COUNTROWS() function In Power BI

Count the number of rows in the table.

Add “Count” Measure In Power BI

This measure returns the Rows Count based on the filter.

• In Home tab, Add a new Measure.

• Set the Measure name and type the below formula

[code language=”HTML”]

ProjectsCount = COUNT(‘TableName'[Column Name])

[/code]

Calculate the Total number of table rows, ignoring the filters In Power BI

Finally, you can add the two measures to any Power BI control as you prefer.

• Power BI.
Conclusion

In this article, I have explained How to calculate the Total number of table rows, ignoring the filters In Power BI?

7 thoughts on “Calculate the Rows Count, Ignoring Filters In Power BI”

1. Simple and great example! I’m newer to Power BI and trying to figure out how I can do this with DISTINCTCOUNT() or COUNTROWS(CALCULATETABLE(DISTINCT()).

I am trying to produce both a filtered count of days with less than 40 orders per day as well as a total count of orders per day (ignoring the filter) in a separate measure so I can layer side-by-side. Clear as mud? Any suggestions appreciated.

Scroll to Top