Forum Discussion

Snap's avatar
Snap
Frequent Visitor
4 years ago
Solved

Fill down based on rank

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. 
 
 

 

 

Need Help
  • Snap's avatar
    Snap
    4 years ago

    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 )
    )

3 Replies

    • Snap's avatar
      Snap
      Frequent Visitor
      4 years ago

      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.

       

  • Snap's avatar
    Snap
    Frequent Visitor
    4 years ago

    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 )
    )