Count number of rows with a value - Answer differs with that of Excel

[karun_r]

I have a dataset that has some 19 columns out of which one column is categorical. It only has "TRUE" and " " as its values. When I try to count the number of occurences of "TRUE" the value returned is more than the actual occurences. The measure that I am using is 

TrueValues = CALCULATE (
    COUNTROWS ( DataSet),
    FILTER (
        DataSet,
        DataSet[CategoricalColumn] = "true"
    )
)

However, I exported the same dataset to excel and did the count using COUNTIF(A1:A3824, "TRUE"). This time the count was correct.

Any idea where the difference would be ? 

[v-caliao-msft · Accepted solution]

@karun_r,

You could use the expression below.
TrueValues = CALCULATE(COUNT(DateSet[categorical]),FILTER(DateSet,DateSet[categorical]="TRUE"))

OR
TrueValues2 = CALCULATE(COUNTROWS(FILTER(ALL(DateSet),DateSet[categorical]="TRUE")))

Regards,

Charlie Liao

[MattAllington]

What is the data type of the column?  The only thing I can think of is that the column is a Boolean column rather an a text column. I fthat is not it, try changing it to Boolean and testing for TRUE