topic Re: Create measure to count instances of a value in DAX Commands and Tips

Create measure to count instances of a value

JJH_ — Sun, 03 Oct 2021 15:45:58 GMT

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:

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

Re: Create measure to count instances of a value

VahidDM — Sun, 03 Oct 2021 16:28:58 GMT

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!!

Re: Create measure to count instances of a value

JJH_ — Sun, 03 Oct 2021 18:16:14 GMT

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?

Re: Create measure to count instances of a value

VahidDM — Mon, 04 Oct 2021 09:36:21 GMT

Hi JJH_

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

Appreciate your Kudos!!

Re: Create measure to count instances of a value

PaulOlding — Mon, 04 Oct 2021 10:59:56 GMT

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')