cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karun_r Member
Member

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

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 ? 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

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

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

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

2 REPLIES 2
Super User
Super User

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

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Moderator v-caliao-msft
Moderator

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

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

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao