Forum Discussion
Data Model Pivot is counting blanks as 1
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.
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.
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