Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karun_r
Employee
Employee

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
v-caliao-msft
Employee
Employee

@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

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

@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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.