Forum Discussion

SunStorm's avatar
SunStorm
Frequent Visitor
2 years ago

Dynamic Ranking based on date and key

Hi all, 

First of all I know that there were similar questions already asked but the results presented there didn't seem to work on my end. 

This is my input data table:

 

 

As you can see I've already created there a static Ranking Column with the following DAX formula:

 

 

 

 

ObjectiveRank = 
    RANKX(
        FILTER(
            table_1,
            table_1[key] = EARLIER(table_1[key])
        ),
        table_1[entry_date],
        ,
        DESC
    )

 

 

 

 

 

And this gave me the correct results when trying to see Count of Key per latest objective status for all dates (rank = 1):

 

Now I'd like to have a slicer that would allow the user to select specific date range.

So, if the date would be e.g. < 2023-12-31 then I'd like to see the following output:

 

because although objective_1 "In Progress" it's not the latest overall it is the latest for a given date range. 

 

How to achieve that? 

Need Help

4 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi SunStorm,

     

    Try the below for your measure:

    Count of Key by Status = 
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR SelStatus = SELECTEDVALUE ( table_1[objective_status] )

-- summarizes the relevant columns, filtering by date
VAR Summarized = 
CALCULATETABLE (
    SUMMARIZE ( table_1, table_1[key], table_1[entry_date], table_1[objective_status] ),
    REMOVEFILTERS (),
    'Calendar'[Date] <= MaxDate
)

-- ranks the summarized table for the latest entry date
VAR Ranked =
ADDCOLUMNS (
    Summarized,
    "Objective Rank",
        ROWNUMBER (
            Summarized,
            ORDERBY ( table_1[entry_date], DESC ),
            DEFAULT,
            PARTITIONBY ( table_1[key] )
        )
)

-- filters the ranked table for only the most recent entry date and matching objective status
VAR Filtered =
FILTER (
    Ranked,
    [Objective Rank] = 1 &&
    [objective_status] = SelStatus
)

RETURN COUNTROWS ( Filtered ) + 0

     

    I added comments in the measure to illustrate the logic, but essentially the measure:

    1. filters for only the relevant columns and records
    2. ranks the table above so we can filter for only the latest entry date per key
    3. filters the above table for only the latest entry dates and relevant objective status
    4. counts the rows in the above table

    This is what my data model looks like:

     

    Let me know if that's not what you were looking for.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

    • SunStorm's avatar
      SunStorm
      Frequent Visitor
      2 years ago

      Hey, 

       

      Thanks for a prompt response. It seems that my version of PowerBI (January 2023) doesn't have the ROWNUMBER function implemented. Would there be any workaround here?

      • Wilson_'s avatar
        Wilson_
        Icon for Memorable Member rankMemorable Member
        2 years ago

        SunStorm,

         

        Is it possible to update your Power BI version? 🙂 (ROWNUMBER was introduced in April 2023 it seems.)

         

        If not, I'm sure there's something you can do with WINDOW, which was introduced in late 2022 if I googled correctly.


        ----------------------------------
        If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)