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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
JJH_
Frequent Visitor

Create measure to count instances of a value

I am working with a bicycle trip table called 'Appended 2019-2021 Trip Data', which contains all information I need for analysis.

 

I am looking to create aggregated measures to count instances of specific values of specific columns.

I wasn't sure how to do this as a calculated measure, and ended up with multiple tables to group by values (e.g. Count Start Day of Week, Count # of Rides from Start Location, etc.).

This approach doesn't make sense when trying to incorporate the counted values in a dashboard.

 

Using the 'Count # of Rides from Start Location' as an example, how can I create a calculated measure to count instances of # of rides started from each unique specified location? (there are 400+ unique starting locations)

Initial thoughts are to use calculate - count - filter on 'from_station_name' but am not sure how to write the formula.

 

Original Table:

Original data tableOriginal data table

 

Aggregated table values that I'm looking to store in a calculated measure:

Aggregation that I'm looking forAggregation that I'm looking for

 

4 REPLIES 4
PaulOlding
Solution Sage
Solution Sage

Hi @JJH_ 

You're on the right lines having a measure to count the number of rides rather than having separate tables holding the results.  However, the measure doesn't need to be as specific as 'Count # of Rides from Start Location', 'Count # of Rides from To Location' etc.  You can have a single 'Count # of Rides' measure, then it's the column(s) you use in your visual that determines how that's split.

 

Count # of Rides = COUNTROWS('Trip Data')

 

VahidDM
Super User
Super User

Hi   @JJH_ 

Try this measue:

Count = COUNT('Trip Data'[from_station_name])
 
Or this one:
Count by filter = VAR _FSN = MAX ( 'Trip Data'[from_station_name] ) RETURN CALCULATE ( COUNTROWS ( 'Trip Data' ), FILTER ( 'Trip Data', 'Trip Data'[from_station_name] = _FSN ) ) 

 

Output: 

 

VahidDM_0-1633278479727.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

JJH_
Frequent Visitor

@VahidDM 

Count = COUNT('Trip Data'[from_station_name]) -->counts # of all unique 'from_station_name', which is 588.

 

 

Count by filter = VAR _FSN = MAX ( 'Trip Data'[from_station_name] ) RETURN CALCULATE ( COUNTROWS ( 'Trip Data' ), FILTER ( 'Trip Data', 'Trip Data'[from_station_name] = _FSN ) ) 

is resulting in an output of 3, which doesn't seem to make sense?power bi.PNG

Hi @JJH_ 

 

Add from_Station_Name to your table then you can find the result.

 

Appreciate your Kudos!!

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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