Create measure to count instances of a value

[JJH_]

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 table

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

Aggregation that I'm looking for

[PaulOlding]

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]

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: 

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

Appreciate your Kudos!!

[JJH_]

@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?

[VahidDM]

Hi @JJH_ 

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

Appreciate your Kudos!!