M-Code not working correctly

Power Query
1

HI DNA Team,

I don’t understand what I did wrong, it is a simple if pattern but the result is incorrect.

image
image961×148 4.61 KB

image
image

I used two patterns:

if ([Vat Input] - [Vat Rec]) = -0.01 then “No Adjust” else if [Vat Input]=0 then “” else “Correction”)

and
if ([Vat Input] - [Vat Rec]) = -0.01 or ([Vat Input] - [Vat Rec]) = 0.01 or [Vat Input] - [Vat Rec] = 0 then “No Adjust” else if [Vat Input]=0 then “” else “Correction”

I am trying to get to say if if ([Vat Input] - [Vat Rec]) = -0.01 then “No Adjust” but it doesn’t work correctly could you please give me a hint?

Thank you,

Matty

2

you probably want to replace your if measure with a SWITCH (TRUE) measure - easier to read.
But, to be really sure what is going wrong with your measure here, it would help the forum to have a slimmed down copy of your data model that duplicates the problem.

Problems with measures can be caused by a variety of things related to your model, table relations, how the other measures are working, etc.

3

Hi @Matty

Try to create an additional Column with this formula [Vat Input] - [Vat Rec] to validate your result.

1 Like
4

I used the sample data in the October version - Does this example get you close to what you want?
if [Sale Price] - [Manufacturing Price] > 1000 then “great”
else if ([Sale Price]-[Manufacturing Price]<1000 or [Sale Price]-[Manufacturing Price] =2) then “not good”
else “OK”

5

@jbressan Thanks it did work, well I noticed that there is a rounding issue :smiley: need to cover the formula using Number.Round().

Thank you,

1 Like