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.

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

      • kumsha1's avatar
        kumsha1
        Post Patron
        5 years ago

        Hi v-janeyg-msft , I tried MAX/MAXX with no luck. Referring to your previous measure formula, my understanding is Table1[Index] is the Index column but What is [Index] ?