Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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')
Hi @JJH_
Try this measue:
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
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?
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |