RANKX on Date Excluding Filter Context

[Anonymous]

Hi,

I am trying to create a little bit of a nuanced RANKX function.

Here's the dataset.

What I need to do is create a RANKX function that ranks the dates in descending order for each job. But it needs to take into account the Project Number context. So ultimately, it will look like this:

Any help is much appreciated!

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

Here the measure:

MeasureCount = 
VAR _helpTable = 
SUMMARIZE ( 
    Table, 
    [Project #],
    "maxDate", CALCULATE ( MAX ( Table[Date] ) )
)
RETURN
SUMX ( Table, CALCULATE ( MAX ( Table[Count] ) , FILTER (_helpTable, Table[Date] = [maxDate] )))

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Thanks Tom!

[tackytechtom]

Hi @Anonymous ,

Another way to solve it is in Power Query:

1) Sort Descending on date 

2) Group by Project #

3) Create a custom column (index):

Here the code:

Table.AddIndexColumn([Projects], "index", 1 )

4) Expand the Custom column:

5) rename and hide the columns you do not need anymore

I think I read somewhere that it is better to create indexes / ranks in Power Query than in DAX, if possible. So this might be the prefered solution compared to the previous solution I posted.

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @Anonymous ,

Here a solution in DAX:

Here the code:

Rank = 
RANKX ( 
    FILTER ( 
        Table1, 
        Table1[Project #] = EARLIER ( Table1[Project #] )
    ),
    Table1[Date],
    , DESC
    , DENSE
)

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Tried recreating but I'm getting this error.

[tackytechtom]

Hi @Anonymous ,

It seems like you were trying to create a measure instead of a calculated column. Can you try it with a calculated column and let me know if it works? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

I've got this so far.

It just still doesn't dynamically change when I filter down to only the Job #1s.

I imagine I need to use some kind of ALLSELECTED somewhere. I just can't figure out where and on exactly what column...

[Anonymous]

You're right 😉. And something I should have mentioned in the original post is that I'm needing it to be dynamic, so I'm guess that a CC won't work. If I filter down to just job one, then I would want each of these to now reflect rank of 1, instead of their original rank.

Really, what I am trying to do is be able to return the Count value by Project based on the the job that has the max date within the current filter context.

So, if there is no filter applied, the count for Project 1 would be 50 and Project 2 would be 20.

However, if I were to filter down to only Job #1s, then the count for Project 1 would be 5 and for Project 2 would be 15.

My thought was that if I could get a dynamic ranking measure, I could then filter to whatever is ranked as 1 (the max date for the project within the current filter context).

Hopefully my tangent makes halfway sense...

[tackytechtom]

Hi @Anonymous ,

Is it this you are looking for? 🙂

Here would be the measure:

MeasureCount = 
VAR _maxDate = CALCULATE ( MAX ( 'Table'[Date] ) )
RETURN
CALCULATE ( MAX ( Table[Count] ),  'Table'[Date] = _maxDate )

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

I think that's getting close. I think I would need these to be blank.

Because ultimately what I need is a card that just returns the sum of the counts based on the newest job for each project. So if nothing is filtered, it would be 70.

And if it's filtered to job 1, it would be 20

I would then need to throw this into a column chart as well that's filtered by the category.