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
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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!