cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

not getting the right count using countrows

I'm using a measure to count the number of rows in a column "Parked at School" that is a "Y". What is happening is the card visualization is showing a much higher number than the actual. If I click on the card, instead of 1 it display 12. It's as if it's counting it by number of Active Date and not by the Parked at School data! I've been on this since last week and can't find a way how to get the true count. Can someone take a look and see what could be wrong? I appreciate it.

# school bus at school =
IF(ISBLANK([School Bus]),0,COUNTROWS(FILTER(VALUES('School Data'),[Parked at School]="Y")))

 

Active DateDriver NameParked at SchoolNumber of School Bus
11/1/2019Joe DoeN1
12/31/2019Tom JonesY2
1/1/2020Mary JonesY1
2/1/2020Tim JonesN1
3/1/2020**bleep** BrownY1
4/1/2020May BrownN1
5/1/2020Joe BrownY1
6/1/2020   
7/1/2020   
8/1/2020   
9/1/2020   
10/1/2020   
11/1/2020   
12/1/2020   
Total8Y8
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: not getting the right count using countrows

Hi @jamaicawi ,

to illustrate my solution:

 

13-05-_2020_00-32-08.pngRegards FrankAT

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: not getting the right count using countrows

Hi @jamaicawi ,

use the following measure:

 

How often parked at School = CALCULATE(COUNT('Table'[Parked at School]);'Table'[Parked at School] = "Y")
 
Regards FrankAT
Highlighted
Helper I
Helper I

Re: not getting the right count using countrows

@FrankAT, the measure you suggested does not work .. it too is giving a skewered calculation, which is counting all the rows and not the rows for the column that has a 'Y' value.

Highlighted
Super User II
Super User II

Re: not getting the right count using countrows

Hi @jamaicawi ,

to illustrate my solution:

 

13-05-_2020_00-32-08.pngRegards FrankAT

View solution in original post

Highlighted
Community Support
Community Support

Re: not getting the right count using countrows

Hi @jamaicawi ,

 

I have tested using the sample data and expression you provided, but the result is no problem.

school.PNG

 

To find the cause of your problem, please share me your .pbix file, removing sensitive information and replace real data with dummy data. It is suggested to upload your file to OneDrive for Business and then paste the link here.

 

 

Best Regards,

Icey

Highlighted
Helper I
Helper I

Re: not getting the right count using countrows

Thanks @Icey . I still don't know what is causing the formula to calculate the way it has been... the subtotals are correct but if i select the individual driver names, if it's 1 school bus in the excel, the PBI dax formula displays it as 12 school buses assigned to the driver. But the subtotal and grand totals are correct. I have accepted FrankAT solution. Your confirmation shows that the formula with the sample data is working. Thanks for helping!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors