Calculate the Rows Count, Ignoring Filters In Power BI

In this article, I will show How to

Calculate the total rows, Ignoring Filters In Power BI

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

Count ALL rows in Power BI


Steps:

Add “Count Rows All” Measure In Power BI

This measure returns the Rows count, ignoring any filters.

  • In Home tab, Add a new Measure.

Add Measure In Power BI

  • Set the Measure name and type the below formula

Count Rows - ALL In Power BI

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

Add Measure In Power BI

  • Set the Measure name and type the below formula

Add Count Measure In Power BI

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

Calculate the total rows, Ignoring Filters In Power BI


Applies To
  • Power BI.
Conclusion

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

See Also

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top