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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

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!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!