Power BI Row Count Issue

[parzival1220]

Hi there,

Please could I ask if there are some common problems / issues that can occur with using DAX to perform row counts in Power BI? Some supporting information:

1. I have a .pbix file with multiple tabs, each tab has it's own SQL script pulling data from our data warehouse (all import queries)
2. The SQL scripts are standalone and are only relevant to their own tab (e.g. data is not shared by multiple tabs)
3. All measures use data from their own tab and do not share data from other tabs
4. In the data modelling view, there is only one relationship setup which connects the data in each of my tabs / datasets to a date table. All of which use the same date range of 24 months and use the same date field
5. Auto relationship detection is switched off

I have a measure that simply counts the number of rows in each tab using this piece of DAX (variable names change from tab to tab of course) - Number of Third Party Withdrawals = COUNTROWS('BT04 (Third Party Withdrawals) - Main') + 0

My problem is that for three of the tabs the row count produce by the DAX is clearly wrong. When I refresh the datasets, I can see the correct number of records being imported by Power BI (486 in this case). Exporting the data from Power BI data grid view also produces 486 rows. The piece of DAX above only counts 388 records. No rows are NULL or blank. 

I've tried using a different piece of DAX but each time I get 388 rows instead of 486. Any help on what may have caused this would be greatly appreciated. 

Many thanks in advance 🙂

[parzival1220]

Apologies @Adamboer / @ppm1 for the late response, I've just picked this back up again. Thanks for the responses. I cannot figure out what's happened, some of the measures are counting correctly and some aren't. When I take a copy of the data from the table within Power BI, it sometimes matches the the row counts in SSMS and sometimes it doesn't. I've removed all the relationships, re-written the DAX, checked the SQL, refreshed the datasets and and the problem remains. I've decided just to rebuild the file from the ground up, one tab at a time. I'll report back and conclude ths thread. In the meantime, thanks to you both 🙂. 

I might dispense with the date table too, it's only relevant for three of my PBI tabs and all the other scripts have their own dates to filter off. I know a date table can be beneficial but maybe it's something for later, I'm nowhere near the skill level of my fellow forum users just yet unfortunately, maybe one day... 😎. 

[Adamboer]

Based on the information you've provided, it seems like the issue may be related to the data relationships in your model. Since you're using a single date table to connect all of your data sources, it's possible that the relationships are not correctly defined or that there are duplicate records in your date table that are causing the row counts to be incorrect.

One thing you can try is to remove the relationship between your date table and the problematic tabs, and see if that fixes the issue. You can also try creating a new date table specifically for each tab and see if that improves the accuracy of your row counts.

Another possibility is that there are some filters or slicers applied to your visuals that are affecting the row counts. Make sure that you've cleared any filters before counting the rows to ensure that you're getting an accurate count.

Finally, you may want to double-check your DAX formula to ensure that it's correctly referencing the table and column names in each tab. It's possible that there's a typo or mistake in the formula that's causing the incorrect row counts.

[ppm1]

If you are showing any of your Date table fields on the report page, are you sure your Date table covers the full range of your data? It may be filtering your data to only the range in your Date table.

Pat