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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 414 members 4,177 guests
Please welcome our newest community members: