Count not calculating correctly

[Del235]

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?

[Del235]

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

[Anonymous]

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. 

[Del235]

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

[Anonymous]

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.

[Anonymous]

@Del235 Did this resolve your issue?

[Del235]

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.

[Del235]

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?

[Anonymous]

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

[Del235]

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

[Anonymous]

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

[Anonymous]

See this example model.

[Del235]

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.

[Anonymous]

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. 

[Del235]

Its only 2400 rows

[Del235]

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

[Anonymous]

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. 

[Del235]

Same result

[Anonymous]

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

[Anonymous]

Try this one out:

Count of Promoter-Detractor for Promoter =
COUNTROWS(

    FILTER('CUSTOMER_FEEDBACK_TABLE',

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

    )

)