Forum Discussion
How to find the First Date using a filter within a Rank Measure
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.
Super UserHi,
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] )
)
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 ) )