cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mim Member
Member

Rank Based on measures with row context.

Hello

 

I am trying to build a measure that ranks the values of another measures ( see attached picture), I know how to do as a calculated column, as you can use the row context ( [TOSTR FORECAST DATE]=EARLIER([TOSTR FORECAST DATE]), but i need to do it as a measure in a pivot table,

 

any idea how to do that, as measures don't support row context, I am using Excel 2013

 

 

 MEASURES.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Rank Based on measures with row context.

@mim@ImkeF@scottsen - interesting problem.

 

@scottsen

I agree with your logic in the last post, and for Excel 2013 I would write the measure like this:

 

 

=
IF (
    HASONEVALUE ( MyTable[ID] ),
    RANKX (
        FILTER (
            ALL ( MyTable[ID] ),
            [TOSTR FORECAST DATE]
                = CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) )
        ),
        MyTable[ID],
        VALUES ( MyTable[ID] )
    )
)
  1. Added a HASONEVALUE check to allow evaluation only for single IDs
  2. Within FILTER, the measure for the currently iterated ID is [TOSTR FORECAST DATE], and this is compared with the measure from the original filter context CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) ).
    Putting VALUES ( MyTable[ID] ) as a filter argument effectively undoes the context transition.
  3. Ranking is just based on ordering of IDs, descending by default. Can be tweaked.

 

Sample workbook here in case useful.

 

Cheers,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




scottsen Senior Member
Senior Member

Re: Rank Based on measures with row context.

Wait, are you trying to tell me you actually understand the 3rd argument to RANKX?  NOBODY DOES!!  Smiley Very Happy


Totally based on Owen's really good thinking, *I* would probably do this slightly differently to avoid the 3rd param:

Rank of ID among those with same TOSTR FORECAST DATE 2:=IF (
HASONEVALUE ( MyTable[ID] ),
  RANKX (
    FILTER (
      ALL ( MyTable[ID] ),
      [TOSTR FORECAST DATE] = CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) )
    ),
    CALCULATE( VALUES( MyTable[ID] ) )
  )
)


8 REPLIES 8
scottsen Senior Member
Senior Member

Re: Rank Based on measures with row context.

Talk about a time I wish I had VAR/RETURN to use.

Just making sure I understand -- and helps my to clarify for somebody with a good idea...

Column 1 is some ID.
Column 2 is the result of a measure (happens to return a date).
Column 3 is a rank of some unseen other measure, but only against rows with same column 2 Date.

Ya?
Highlighted
Super User
Super User

Re: Rank Based on measures with row context.

Hi mim,

just to check understanding: You need the Rank as a measure, but the [TOSTR FORECAST DATE] is a column, so Rank per [TOSTR FORECAST DATE]-day?

 

Or is the date a measure as well?

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mim Member
Member

Re: Rank Based on measures with row context.

yes TOSTR FORECAST DATE is a measure basically  if there is one unique date then the rank will be 1, if the same date is repeated 5 times then the rank should 1,2,3,4,5 the order is not important

scottsen Senior Member
Senior Member

Re: Rank Based on measures with row context.

Ignoring the difficulties in iterating over only rows that return the same measure value -- you have another problem.  RankX can't return different ranks for the same value.  I've only dealt with this by "cheating" -- and forcing tiny (almost random) variation in the value I am ranking.

 

At the highest level, we want:

= RANKX (
     [[All Rows That We Want to Rank Against Each Other]],
     [[measure value to rank, with some way of removing ties]]
 )


The rows we want to rank against each other are... ALL rows that have the same measure value as the CURRENT row.

I'm gonna cheat and use variables, then hopefully we can figure out how to convert it ?

TheRank =
  VAR MyDate = [MeasureThatGivesTheDate]
  VAR MyRows = FILTER( ALL(MyTable[RowId]), [MeasureThatGivesTheDate] = MyDate )
  RETURN RANKX(MyRows, CALCULATE ( MIN (MyTable[RowId] ) ) )

At least, that works in my head... (using RowId to force an arbitrary ranking of all the MyRows)

Super User
Super User

Re: Rank Based on measures with row context.

@mim@ImkeF@scottsen - interesting problem.

 

@scottsen

I agree with your logic in the last post, and for Excel 2013 I would write the measure like this:

 

 

=
IF (
    HASONEVALUE ( MyTable[ID] ),
    RANKX (
        FILTER (
            ALL ( MyTable[ID] ),
            [TOSTR FORECAST DATE]
                = CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) )
        ),
        MyTable[ID],
        VALUES ( MyTable[ID] )
    )
)
  1. Added a HASONEVALUE check to allow evaluation only for single IDs
  2. Within FILTER, the measure for the currently iterated ID is [TOSTR FORECAST DATE], and this is compared with the measure from the original filter context CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) ).
    Putting VALUES ( MyTable[ID] ) as a filter argument effectively undoes the context transition.
  3. Ranking is just based on ordering of IDs, descending by default. Can be tweaked.

 

Sample workbook here in case useful.

 

Cheers,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




scottsen Senior Member
Senior Member

Re: Rank Based on measures with row context.

Wait, are you trying to tell me you actually understand the 3rd argument to RANKX?  NOBODY DOES!!  Smiley Very Happy


Totally based on Owen's really good thinking, *I* would probably do this slightly differently to avoid the 3rd param:

Rank of ID among those with same TOSTR FORECAST DATE 2:=IF (
HASONEVALUE ( MyTable[ID] ),
  RANKX (
    FILTER (
      ALL ( MyTable[ID] ),
      [TOSTR FORECAST DATE] = CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) )
    ),
    CALCULATE( VALUES( MyTable[ID] ) )
  )
)


mim Member
Member

Re: Rank Based on measures with row context.

Guys thanks a lot, that's why PowerBi rocks !!! @scottsen@OwenAuger@ImkeF

Ola Frequent Visitor
Frequent Visitor

Re: Rank Based on measures with row context.

Just for the record...

This Video goes through the formula: https://youtu.be/sfJWoQixi2U?list=PLrRPvpgDmw0nglJ9yX2XT5-K1A_AKHpvW

And this video shows the advantage of ALLSELECTED() : https://www.youtube.com/watch?v=z2qzJVeYhTY