Forum Discussion

VInciDa's avatar
VInciDa
Regular Visitor
4 years ago
Solved

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

 

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!

  • 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.

     

6 Replies

  • 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's avatar
      VInciDa
      Regular Visitor

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User

        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 )
            )
  • VInciDa's avatar
    VInciDa
    Regular Visitor

    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. 

    WorkoutIdSetExerciseLift(lbs)RepDateDistance  
    11Squat136106/6/2022   
    12Squat136106/6/2022   
    13Squat13646/6/2022   
    21Squat136106/7/2022   
    22Squat136106/7/2022   
    21Bench Press10086/7/2022   
    22Bench Press10086/7/2022   
    31Squat13646/8/2022   
    32Squat13646/8/2022   
    33Squat13646/8/2022   
    31Push Press80106/8/2022   
    32Push Press80106/8/2022   
    33Push Press75106/8/2022   
    31Bicycling  6/8/202210000  
    32Bicycling  6/8/202220000  
    41Squat13846/9/2022   
    41Bench Press12056/9/2022   
    42Bench Press12556/9/2022   
    43Bench Press13056/9/2022   
    41Rowing1 6/9/20225000  
    42Rowing  6/9/20225000  
    51Squat13846/10/2022   
    52Squat13846/10/2022   
    61Squat13746/11/2022   
    71Push Press20046/12/2022   
    71Bicycling  6/12/20226000  

    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's avatar
      Jihwan_Kim
      Icon for Super User rankSuper 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.

       

      • VInciDa's avatar
        VInciDa
        Regular Visitor

        This is exactly what I needed thank you