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

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
Community Champion
Community Champion

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

VahidDM
Community Champion
Community Champion

Hi @JJH_ 

 

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

 

Appreciate your Kudos!!

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors