How to find the First Date using a filter within a Rank Measure

[VInciDa]

I have created [Rank] as a measure to capture only the TOP 2 [ Lifts] including ties while making everything else blank. The goal is to create the [First_Date_Rank_2] column that shows the Earliest Date where Rank = 2, in this case is would be 6/7/2022 and place it in the same row where Rank = 1. If I can accomplish this then I can do a DATE DIFF to show [Days Since Last PR], this would result in the duration of days it took for this individual to achieve a higher Lift(lbs).

Thank you in advance!

[VInciDa]

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_Kim · Accepted solution]

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.

[VInciDa]

This is exactly what I needed thank you

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Top 2 by lift measure: = 
VAR _currentexcercise =
    MAX ( Data[Exercise] )
RETURN
    COALESCE (
        IF (
            HASONEVALUE ( Data[Date] ),
            CALCULATE (
                SUM ( Data[Lift(lbs)] ),
                KEEPFILTERS (
                    TOPN (
                        2,
                        FILTER ( ALL ( Data ), Data[Exercise] = _currentexcercise ),
                        CALCULATE ( SUM ( Data[Lift(lbs)] ) ), DESC
                    )
                )
            )
        ),
        ""
    )

First day of rank 2 measure: =
VAR _currentexcercise =
    MAX ( Data[Exercise] )
VAR _toptwotable =
    GROUPBY (
        FILTER (
            ADDCOLUMNS (
                FILTER ( ALL ( Data ), Data[Exercise] = _currentexcercise ),
                "@toptwo", [Top 2 by lift measure:]
            ),
            [@toptwo] <> BLANK ()
        ),
        Data[Date],
        "@ranktwo", MINX ( CURRENTGROUP (), [@toptwo] )
    )
RETURN
    IF (
        CALCULATE ( MAX ( Data[Date] ), ALL () ) = MAX ( Data[Date] )
            && HASONEVALUE ( Data[Date] ),
        MINX ( _toptwotable, Data[Date] )
    )

[VInciDa]

This is my first question I ever asked in Power Bi Community so thank you for the quick response. What you provided is exactly what I needed for that small example but when adding new rows of data it is not what I actually want. I will try better explaining to not waste your time.

Using your DAX formula I get the following with new data results:

I added  another two rows after the "Top 1 Lift"  [Rank]=1 ,  Lift[lbs] = 138 and occured on 6/9/2022. The first row added Lift(lbs) = 138 with the date 6/10/2022 making it also [Rank] =1 and the second row added Lift(lbs) = 137 making it the new [Rank] =2 and occurs 6/9/2022.  The problem is that the Dax formula wants to always add the [First day of rank 2 measure] on the last Date row.

The result I want should look like this:

On 6/6/2022 this was the first time achieving the highest [Lift(lbs)]= 136 which made it [Rank]= 1. On 6/9/2022 is the first time achieving the highest [Lift(lbs)]=138 making it now  [Rank]=1, which now makes 6/6/2022 [Rank] = 2. On 6/10/2022 is not the first time achieving the highest [Lift(lbs)]=138 making it also [Rank] =1. On 6/11 because the [Lift(lbs)]=137 it is considered [Rank] = 2.  I want to see the how many days it took to surpass the [Rank] = (First previous 2) which occured 6/6/2022 vs the First date [Rank] = 1 which occured on 6/8/2022. 

Thank you again in advance

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Could you please check the below picture and the attached pbix file whether it shows the expected results?

Conditional Ranking by lift measure: = 
VAR _currentexcercise =
    MAX ( Data[Exercise] )
VAR _maxlift =
    CALCULATE ( MAX ( Data[Lift(lbs)] ), ALL () )
VAR _firstofmaxliftdate =
    CALCULATE (
        MIN ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Lift(lbs)] = _maxlift )
    )
VAR _ranking =
    RANKX (
        FILTER (
            ALL ( Data ),
            Data[Exercise] = _currentexcercise
                && Data[Date] <= _firstofmaxliftdate
        ),
        CALCULATE ( SUM ( Data[Lift(lbs)] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( MAX ( Data[Date] ) <= _firstofmaxliftdate, _ranking, "" )

First day of rank 2 measure: = 
VAR _newtable =
    FILTER (
        ADDCOLUMNS ( ALL ( Data ), "@ranking", [Conditional Ranking by lift measure:] ),
        [@ranking] = 2
    )
VAR _toprankfirstdate =
    MINX (
        FILTER (
            ADDCOLUMNS ( ALL ( Data ), "@ranking", [Conditional Ranking by lift measure:] ),
            [@ranking] = 1
        ),
        Data[Date]
    )
RETURN
    IF ( MAX ( Data[Date] ) = _toprankfirstdate, MINX ( _newtable, Data[Date] ) )

How many days: = 
VAR _toprankfirstdate =
    MINX (
        FILTER (
            ADDCOLUMNS ( ALL ( Data ), "@ranking", [Conditional Ranking by lift measure:] ),
            [@ranking] = 1
        ),
        Data[Date]
    )
RETURN
    IF (
        MAX ( Data[Date] ) = _toprankfirstdate,
        DATEDIFF ( [First day of rank 2 measure:], _toprankfirstdate, DAY )
    )