topic Re: Count not calculating correctly in DAX Commands and Tips

Count not calculating correctly

Del235 — Tue, 22 Aug 2023 17:09:35 GMT

I am trying to count text in a column but I'm getting incorrect values. Example: I have two columns

Visit Date Attendance	Promoter-Detractor
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Detractor
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Neutral
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter
7/9/2023 0:00	Promoter

When I use the formula:

Count of Promoter-Detractor for Promoter =

CALCULATE(

COUNTA('CUSTOMER_FEEDBACK_TABLE'[Promoter-Detractor]),

'CUSTOMER_FEEDBACK_TABLE'[Promoter-Detractor] IN { "Promoter" }

)

I get a count of 23 for Promoter when it should be 11. Why?

Re: Count not calculating correctly

Anonymous — Tue, 22 Aug 2023 20:04:58 GMT

Try this one out:

Count of Promoter-Detractor for Promoter =
COUNTROWS(

FILTER('CUSTOMER_FEEDBACK_TABLE',

'CUSTOMER_FEEDBACK_TABLE'[Promoter-Detractor] = "Promoter"

)

Re: Count not calculating correctly

Del235 — Tue, 22 Aug 2023 20:27:41 GMT

Same result

Re: Count not calculating correctly

Anonymous — Tue, 22 Aug 2023 20:34:00 GMT

You're using a DAX measure? That tested successfully for me...

Count of Promoter-Detractor for Promoter = COUNTROWS( FILTER('CUSTOMER_FEEDBACK_TABLE', 'CUSTOMER_FEEDBACK_TABLE'[Promoter-Detractor] = "Promoter" ) )

Re: Count not calculating correctly

Del235 — Tue, 22 Aug 2023 21:02:56 GMT

Is my issue related to the fact that I'm trying to get the count by Date?

Re: Count not calculating correctly

Anonymous — Tue, 22 Aug 2023 21:07:34 GMT

Yes, if you're simply looking for a count of rows where the "Promoter-Detractor" has a value of "Promoter" only, use the forumla shared and it will work just fine. You want to filter off of that column, not the date column.

Re: Count not calculating correctly

Del235 — Tue, 22 Aug 2023 21:12:50 GMT

So there are other dates. So I would be counting the number of "Promoters" by date in the column Promotor-Detractor.

Re: Count not calculating correctly

Anonymous — Tue, 22 Aug 2023 21:20:15 GMT

It would be the same measure/formula. You would just use your date field in your visual or table as a column or x-axis. For this example, I used the same data you shared but changed a couple of the dates so that you can see the break of the count measure among dates.

Notice I used the same measure I shared, but now I pulled in the date column. This counts the number of "Promoter"s by date.

Re: Count not calculating correctly

Anonymous — Wed, 23 Aug 2023 13:12:14 GMT

Del235 Did this resolve your issue?

Re: Count not calculating correctly

Del235 — Wed, 23 Aug 2023 17:21:57 GMT

No. Something else is going on. Even if I do a count on the column Promoter-Detractor I dont get the same answer as I would if I export the data into excel.

Re: Count not calculating correctly

Del235 — Wed, 23 Aug 2023 19:40:20 GMT

If I export the Data Table to Excel, then import it into power bi, I get different results. See below:

The one on the left is correct. Both tables have the the column Promotor-Detractor as text. Why the different results?

Re: Count not calculating correctly

Anonymous — Wed, 23 Aug 2023 19:46:33 GMT

Will you share a copy of the example pbix file you're working with?

Re: Count not calculating correctly

Del235 — Wed, 23 Aug 2023 20:05:34 GMT

It has PII info in it, and I'm using DirectQuery. I'll see if I can do it another way.

Re: Count not calculating correctly

Anonymous — Wed, 23 Aug 2023 20:15:18 GMT

Just a generic copy with sample data is perfect. I can always create one as well and send it over.

Re: Count not calculating correctly

Anonymous — Wed, 23 Aug 2023 22:12:47 GMT

See this example model.

Re: Count not calculating correctly

Del235 — Thu, 24 Aug 2023 19:12:39 GMT

So...

We store data on Snowflake. I am connecting Power BI to Snowflake and use DirectQuery to connect to it. Today I downloaded the data from snowflake and opened in Excel. It ma

tches the totals I get in Power BI. BUT... The example I used was from a table I created in PBI using all of the columns with no filters. That was the example I used above. Turns out the Original formula I used was correct because it is seeing all of the data. The export of the table is missing data but there are no filters (I Swear). So there isn't a formula problem but why isn't all of the data appearing.

See the data below:

Snowflake		PBI-Export
Date	Promoter-Detractor		Date	Promoter-Detractor
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Detractor
7/9/2023	Neutral		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Neutral
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter		7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Neutral
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Detractor
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Promoter
7/9/2023	Promoter

There are no filters but I can't figure out why the other rows don't appear but the formula counting the rows sees them and counts them.

Re: Count not calculating correctly

Anonymous — Thu, 24 Aug 2023 19:25:42 GMT

Right... a couple of things here. It does sound like either formula will work. If the data you are exporting to Excel via a visual is over a certain number of rows, that is why. Power BI has a row limit for exporting. There are ways around this. I use a combination of Azure data studio and Excel to get mass amounts of data from Power BI. Raw data wise, you can try copying the loaded table from the Table view mode of Power BI (right click table name, copy) and paste it into Excel. This can be quite taxing depending on the amount of data, however.

Re: Count not calculating correctly

Del235 — Thu, 24 Aug 2023 20:17:36 GMT

Its only 2400 rows

Re: Count not calculating correctly

Del235 — Thu, 24 Aug 2023 20:22:10 GMT

If I open the table in Power Query I see all 2400 rows...

Re: Count not calculating correctly

Anonymous — Thu, 24 Aug 2023 20:29:16 GMT

If you're in Power Query that's just a preview of the data. Load it into your model and look at the table of data in the Table view mode in model view.