cancel
Showing results for
Did you mean:
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

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

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

2 REPLIES 2
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

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

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

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 414 members 4,177 guests
Recent signins: