Data Model Pivot is counting blanks as 1

[mantilla.mariana]

I have countif formula, that generates a 1 when one date is greater than another. However, in my data model pivot table it is counting all the blank values as well.

Any way around this?

[#2]

Hi @mantilla.mariana,

Your question is not clear to me.

Can you explain or put screen shot and tell what you want.

[#3 · Accepted solution]

Hi,

As i understood you use formula like

=IF(date1>date2,1,"")

and count this field in pivot table. Yes, it will count everything includes "". You may apply same field as a filter to pivot table and unfilter empty values to receive correct result.

Alternatively you may use formula

=--(date1>date2)

which returns 1 or 0, apply to these cells custom format

0;0;;@

to make zeroes invisible, refresh pivot table (to shift from text field type to numeric) and use Sum instead of Count.

Something like this.

[#4]

Perhaps one more option - if you add your pivot table to data model when created, you may add measure to it like

=CALCULATE(COUNTROWS(yourTable),yourFiled="1")

and use this measure in pivot table

[#5]

Thanks so much ..you're a life saver :)