Forum Discussion

kumsha1's avatar
kumsha1
Post Patron
5 years ago
Solved

Fill down the column values with a calculated column

Hi,

 

Can someone please help with the DAX to fill down the values for TONNES_LINE, so the 0 will be replaced by the actual value...it's required in a calculated column. Thanks !

 

     

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi， kumsha1 

     

    It’s my pleasure to answer for you.

    According to your description, I think you can create a measure to calculate the correct result.

    Like this:

     

    Measure =
IF (
    SELECTEDVALUE ( Table1[TONNES_LINE] ) = 0,
    VAR a =
        MAXX (
            FILTER (
                ALL ( Table1 ),
                [Index] < SELECTEDVALUE ( Table1[Index] )
                    && Table1[TONNES_LINE] <> 0
            ),
            [Index]
        )
    RETURN
        MAXX ( FILTER ( ALL ( Table1 ), Table1[Index] = a ), Table1[TONNES_LINE] ),
    SELECTEDVALUE ( Table1[TONNES_LINE] )
)

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    please provide the sample data in usable format. Does it have to be DAX?  Do you want a calculated column or a measure?

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    Hi kumsha1,

     

    Are you looking to replace the 0 with the prior row value of either progressive_ton_start or progressive_tonnes if there is a value?

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Hi kumsha1 ,

       

      Can you try creating the following columns

       

       

      val = if([PROGRESSIVE_TON_START] <> 0, [PROGRESSIVE_TON_START], [PROGRESSIVE_TONNES])

       

      TONNES_LINE = 
var from_date = [FROM_DATE]
var prior_ton_time =
if([val] = BLANK(), CALCULATE(MAX([FROM_DATE]), FILTER(ALL('Table'), [FROM_DATE]<= from_date && [val] <> BLANK())), from_date)
return
CALCULATE(MAX([val]), FILTER(ALL('Table'), [FROM_DATE]>=prior_ton_time && [FROM_DATE] <=from_date))

       

      Hope this Helps,
      Richard
      Did I answer your question? Mark my post as a solution!
      Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

       

      • kumsha1's avatar
        kumsha1
        Post Patron
        5 years ago

        Hi richbenmintz , I tried your formulas and the TONNES_LINE was never executed with an error not enough memory.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi， kumsha1 

     

    It’s my pleasure to answer for you.

    According to your description, I think you can create a measure to calculate the correct result.

    Like this:

     

    Measure =
IF (
    SELECTEDVALUE ( Table1[TONNES_LINE] ) = 0,
    VAR a =
        MAXX (
            FILTER (
                ALL ( Table1 ),
                [Index] < SELECTEDVALUE ( Table1[Index] )
                    && Table1[TONNES_LINE] <> 0
            ),
            [Index]
        )
    RETURN
        MAXX ( FILTER ( ALL ( Table1 ), Table1[Index] = a ), Table1[TONNES_LINE] ),
    SELECTEDVALUE ( Table1[TONNES_LINE] )
)

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • kumsha1's avatar
      kumsha1
      Post Patron
      5 years ago

      Hi v-janeyg-msft , i tried creating above measure but it never returned any results and having performance impact on the report. Previously i was able to create RunningMax for other columns for similar requirement but when i try to do the same for TONNES_LINE column i am getting below error. Any further help on this is much appreciated.

       

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        5 years ago

        Hi, kumsha1 

         

        Use 'calculate' in column is not a good choice because it may cause error,try to change to 'maxx'.

         

        Best Regards

        Janey Guo