Subtraction only if result is not X

[Anonymous]

Hi all,

I had a question I want to subtract a fixed value from a column BUT only when the result is => 5, so for example:

If my fixed value is 5 I want the result to look like this: (I want this result as a calculated column)

20	15
30	25
25	20
0	0
50	45
5	5

Thanks in advance !

Best Regards,

L.Meijdam

[WolfBiber]

Try following code, you need to change Source here of course, FixedParam is a Parameter of type Number

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQ0lEyNFWK1YlWMgaxjSBsIKUDkgSxQcIQlimIaQJRAVIAZMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] > FixedParam then [Column1]-5 else [Column1])
in
    #"Added Custom"

[jthomson · Accepted solution]

newcolumnname=if([existingcolumnname]<5,[existingcolumnname],[existingcolumnname]-5) should work

[Anonymous]

Hi @jthomson,

I get the error message stating "The end of input was reached."

Hi@WolfBiber,

If possible I am looking for a solution in DAX

Best regards,

L.Meijdam

[WolfBiber]

then use formula from @jthomson and replace , with ; 

newcolumnname=if([existingcolumnname]<5;[existingcolumnname];[existingcolumnname]-5)

[Anonymous]

Hi @WolfBiber,

I already do that by default so it is not making a difference.

Best regards,

L.Meijdam

[v-sihou-msft]

@Anonymous

The formula provided by @WolfBiber and @jthomson should work. You need to add this calculated column outside of Query Editor. 

Regards,

[Anonymous]

Hi @v-sihou-msft, @WolfBiber, @jthomson

I am not sure what I did but the code is returning no errors anymore .. I made no changes.

But anyway thanks for the help the code provided works like expected now !

Best Regards,

L.Meijdam