How to add a row count to your table

[JackFrench]

I originally had a separate card on my report that would display the COUNTROWS(table). However, I found out from the DBA that in order to retrieve that number, it was rerunning the entire query. In an effort to minimize the workload on the database, I tried to include a count in the query itself. I added: 

(COUNT(*) OVER() ) AS RecordCount

This returned the correct row count for the original query, however, it does not adjust dynamically within Power BI after making selections on the slicers in the report. For example, my query returns 14,900 rows. I can see this value in each row under the newly created column "RecordCount". But, when I adjust the date range using a slicer, the value of 14,900 still shows in that column rather than the 500 it filtered down to. Any ideas on how to get this value to dynamically adjust to my slicers without having a function that will rerun the entire query?

[v-yiruan-msft]

Hi @JackFrench ,

Based on your description, it appears you are trying to obtain a row count that dynamically adjusts based on slicer selections without rerunning the entire query. It seems you are currently using a calculated column. Calculated columns are computed only once during data import and do not respond to slicer interactions or other visual-level filters in Power BI. This is why your RecordCount value remains the same regardless of slicer adjustments.

To achieve a dynamic row count that adjusts based on slicer selections, you should use a measure instead of a calculated column. Measures are recalculated based on the current filter context, making them responsive to slicers and other filters in your report.

DynamicRowCount = COUNTROWS('Table')

Please review the following links for more information on the differences between calculated columns and measures...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

Best Regards

[JackFrench]

I had a card with COUNTROWS(tablename) so the end user could view the count of rows their data model returns. My DBA recently told me that each time they open up/refresh the report, that function reruns the ENTIRE query against the database. Assuming this is true, I need to find a workaround since the underlying query consists of 200+ million rows and it is slowing down run time for all parties.

[lbendlin]

Have your DBA create a view with the row counts of the tables/queries you are interested in.  Consume that view.

[lbendlin]

Please provide more details.  Is this a Direct Query or Import Mode data source?  What's the purpose of the row count ?

[JackFrench]

This is using DirectQuery Mode. My end user would like to view how many rows are in the data model. For the longest time, I had a card with COUNTROWS(tablename) so the end user could view the count of rows their data model returns. My DBA recently told me that each time they open up/refresh the report, that function reruns the ENTIRE query against the database. Assuming this is true, I need to find a workaround since the underlying query consists of 200+ million rows.