Fill down the column values with a calculated column

[kumsha1]

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 !

     

[v-janeyg-msft · Accepted solution]

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]

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]

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]

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] ?

[v-janeyg-msft]

Hi, @kumsha1 

In order to calculate the result more conveniently, you may need to create a index column in PQ first,then create the msasure I mentioned.

Like this:

Best Regards

Janey Guo

[richbenmintz]

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]

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]

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

[vivran22]

Hey @kumsha1 ,

You may try this as calculated column:

Tonne_Line =
IF (
    Table[Progressive_Ton_start] = 0
        && NOT ISBLANK ( Table[Progressive_Tonnes] ),
    Table[Progressive_Tonnes],
    Table[Progressive_Ton_start]
)

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[lbendlin]

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