Forum Discussion
How to find the First Date using a filter within a Rank Measure
- 4 years ago
Hi,
Please check the below picture and the attached pbix file.
All measures are in the attached pbix file.
I tried to think in a different way to solve the problem than the previous one.
I hope this helps to provide an easier way to solve the problem.
In the below picture, the reason why Rowing exercise shows the top on date is because there is the information of the lift in one of the rows of Rowing.
The dax you created works for the small sample that I provided. When I add different type of exercises using your DAX formulas I get the following results:
I dont know how to apply an attachment or a power bi attachment but here is the table in which the following result is referencing. The main difference from the reference below is that there could be 100+ types of [Exercise] not just "Squat", and during a workout each day an multiple exercises can be completed more than once as seen in [Set] column. This next difference is that some exercises dont require lifting so the [Lift(lbs)] = blank, but this will not need to be addressed in the result for I will just filter out anything where the [Lift(lbs)] = blank.
| WorkoutId | Set | Exercise | Lift(lbs) | Rep | Date | Distance | ||
| 1 | 1 | Squat | 136 | 10 | 6/6/2022 | |||
| 1 | 2 | Squat | 136 | 10 | 6/6/2022 | |||
| 1 | 3 | Squat | 136 | 4 | 6/6/2022 | |||
| 2 | 1 | Squat | 136 | 10 | 6/7/2022 | |||
| 2 | 2 | Squat | 136 | 10 | 6/7/2022 | |||
| 2 | 1 | Bench Press | 100 | 8 | 6/7/2022 | |||
| 2 | 2 | Bench Press | 100 | 8 | 6/7/2022 | |||
| 3 | 1 | Squat | 136 | 4 | 6/8/2022 | |||
| 3 | 2 | Squat | 136 | 4 | 6/8/2022 | |||
| 3 | 3 | Squat | 136 | 4 | 6/8/2022 | |||
| 3 | 1 | Push Press | 80 | 10 | 6/8/2022 | |||
| 3 | 2 | Push Press | 80 | 10 | 6/8/2022 | |||
| 3 | 3 | Push Press | 75 | 10 | 6/8/2022 | |||
| 3 | 1 | Bicycling | 6/8/2022 | 10000 | ||||
| 3 | 2 | Bicycling | 6/8/2022 | 20000 | ||||
| 4 | 1 | Squat | 138 | 4 | 6/9/2022 | |||
| 4 | 1 | Bench Press | 120 | 5 | 6/9/2022 | |||
| 4 | 2 | Bench Press | 125 | 5 | 6/9/2022 | |||
| 4 | 3 | Bench Press | 130 | 5 | 6/9/2022 | |||
| 4 | 1 | Rowing | 1 | 6/9/2022 | 5000 | |||
| 4 | 2 | Rowing | 6/9/2022 | 5000 | ||||
| 5 | 1 | Squat | 138 | 4 | 6/10/2022 | |||
| 5 | 2 | Squat | 138 | 4 | 6/10/2022 | |||
| 6 | 1 | Squat | 137 | 4 | 6/11/2022 | |||
| 7 | 1 | Push Press | 200 | 4 | 6/12/2022 | |||
| 7 | 1 | Bicycling | 6/12/2022 | 6000 |
I was hoping to avoid this because of the complexity on explaining my needs without being too confusing but this next result should provide the solution to my problem.
The result I need is below:
As stated above the only two differences that need to be addressed: First is that there is more than one [Exercise] and each one has their own ranking with themselves. [Exercise] = "Squat" has to compare ranking with only "Squat". [Exercise] = "Bench Press" has to compare ranking with "Bench Press" and so on for the rest of the exercises that are added to the table. Second, for me to capture the accurate [Lift(lbs)] I use MAX which gets groups all the different times this exercise was performed "set" by Exercise and the Date. For example for the day 6/8/2022 [Exercise] = "Push Press" : [Set] = 1 [Lift(lbs)] = 80, [Set] = 2 [Lift(lbs)] = 80, [Set] = 3 [Lift(lbs)] = 75. So the table below shows the Max [Lift(lbs)] which = 80 for that day 6/8/2022.
As seen above visual, There are three dates under column [ First day of rank 2] and three values under [How many days] because in this example there are only three exercises (there can be 100+ exercises). Similar to how you figured out how to capture [Conditional Ranking by Lift Measure:] and [First day of Rank 2] and [How many days:] for one exercise "Squat" I need to do the same for all the different types of excercises that get entered in the data source.
Thanks again for your quick responses and patience as I am learning how to ask the correct questions. Hopefully the previous steps can be used to better explain the added complexity to the problem.
- Jihwan_Kim4 years ago
Super User
Hi,
Please check the below picture and the attached pbix file.
All measures are in the attached pbix file.
I tried to think in a different way to solve the problem than the previous one.
I hope this helps to provide an easier way to solve the problem.
In the below picture, the reason why Rowing exercise shows the top on date is because there is the information of the lift in one of the rows of Rowing.
- VInciDa4 years agoRegular Visitor
This is exactly what I needed thank you