CODEWITHSUNDEEP
excelcountpowerpivotdax
Rhonda
Rhonda

Reputation: 1721

DAX Count function making mistake somewhere

File: count.xlsx located on GitHub repository

Software: MS Excel 2016 Power Pivot

I know for fact there are 10,921 rows in EXCEL sheet.

When I create DAX measure Total_Incidents:=Count(Graffiti[CREATED_DATE]) value comes to 10,921. I count the CREATED_DATE because there is no NULL value.

There are three statuses, Open, Pending, Closed which are calculated as follows

Total_Closed:=sumx(FILTER(Graffiti,Graffiti[STATUS]="Closed"),[Total_Incidents])

Total_Closed= 5354, <- correct

However, Total_Opened is incorrect

Total_Opened:=sumx(FILTER(Graffiti,Graffiti[STATUS]="Open"),[Total_Incidents])

Total_Opened= 4483 it is supposed to be 4481

However Total_Pending is correct

Total_Pending:=sumx(FILTER(Graffiti,Graffiti[STATUS]="Pending"),[Total_Incidents])

Total_Pending= 75, <- correct

When I add totals I get 2 more incidents because of Total_Opened

Total_Calc:=[Total_Closed]+[Total_Opened]+[Total_Pending]

Total_Calc= 10923 <- incorrect, should be 10921

Why the discrepancy in Total_Opened? Cannot figure this.

enter image description here

Upvotes: 0

Views: 90

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5202

I had originally answered with this:

"I know you said you count CREATED_DATE because there is no NULL value, but did you check for blanks in your CREATE_DATE column? I duplicated your problem by having blank dates. You could have 2 blank dates.

You could use COUNTBLANK(Graffiti[CREATED_DATE]) to check if you do have blanks."

Then I noticed you had a link to your Excel file on GitHub, so...

I downloaded it and I looked for blanks in your dates--there were none.

So I added columns for Total_Closed, Total_Opened, Total_Pending and Total_Calc. (I used your formulas, but instead of sumx, I used countx in each formula, so I could just compare row counts.)

Total_Incidents:=Count(Graffiti[CREATED_DATE])

Total_Closed:=countx(FILTER(Graffiti,Graffiti[STATUS]="Closed"),[Total_Incidents])

Total_Opened:=countx(FILTER(Graffiti,Graffiti[STATUS]="Open"),[Total_Incidents])

Total_Pending:=countx(FILTER(Graffiti,Graffiti[STATUS]="Pending"),[Total_Incidents])

Total_Calc:=[Total_Closed]+[Total_Opened]+[Total_Pending]

Here's what I got:

Total_Incidents: 10921

Total_Closed: 6365

Total_Opened: 4481

Total_Pending: 75

Total_Calc: 10921

These counts look correct.

I'm guessing you figured out and corrected your problem.

Upvotes: 1

Related Questions