Fill down based on rank

[Snap]

Hi, I have the below table. I am trying to fill down the empty categories. The current dax formula I am trying get working is:

Category fill down = if('Table'[Category]=blank(),CALCULATE(lastNONBLANK('Table'[Category],'Table'[Category]),FILTER(ALL('Table'),'Table'[Rank]<=EARLIER('Table'[Rank]))),'Table'[Category])

However it seems to return the lastnonblank in the whole table instead of the previous rank value. So in the below table rank 520 should be 'P gifts' and 536 should be 'MPS'. I basically need it to fill down the blanks with the previous highest rank untill it finds a new previous highest rank. 

[Snap · Accepted solution]

Managed to solve with the below:

VAR LastNonBlankDate =

CALCULATE (

LASTNONBLANK ( 'Table'[Rank], 1 ),

FILTER (

ALL ( 'Table' ),

'Table'[Rank] <= EARLIER ( 'Table'[Rank] )

&& NOT ( ISBLANK ( 'Table'[Category]) )

)

)

RETURN

CALCULATE (

FIRSTNONBLANK('Table'[Category],'Table'[Category]) ,

FILTER ( ALL ( 'Table' ), 'Table'[Rank] = LastNonBlankDate )

)

[amitchandak]

@Snap , Try a new column like

New column =
var _min = Minx(filter(Table, [Rank] > earlier([Rank])), [Rank])
return
if(isblank([category]), maxx(filter(Table, [Rank] =_min ), [category]),[category])

You can also explore fill up in power query

https://docs.microsoft.com/en-us/power-query/fill-values-column

[Snap]

@amitchandak - thank you for your reply. Correcting myself. It is filling up instead of down - I managed to fix that.

However I realised it is only filling down one value instead of the next nonblank value. 

I know I can use PowerQuery but this has to be done in dax in this instance.