Two action on a 'then' or 'else' potion of IF-THEN statement

Hi Community,

Normally in an if statement, you can use logical operaters (AND, OR, NAND, NOR) on the IF portion of a statement / formula,

But I want to evaluate just one condiction in the IF statement but do two or more actions....basically I want to say.....

if [field1] = 1000

then

[field1]=[field1]/3 AND [ROW+1:field1] = [field1]/3 AND [ROW+2:field1] = [field1]/3

else

[filed1]

endif

I know that I need to use the multi-row tool.

Hopefully someone out there knows.

thanks

-prpatel.

Designer Cloud

Tips and Tricks

Power BI

Accepted answers

prpatel

Actually...these are not what I am looking for.....

I want to say...

if [filed1] = Y

then

[Field2]= "Happy"

and

[Field3] = " Not sad"

else

[Field2]="Sad"

and

[Field3]="Not Happy"

endif

Ironically, as I write this I realize that what I need if two different if statements.

-prpatel.

All comments

Yoshiro_Fujimori

@prpatel ,

Mutil-Row Formula updates the row currently in focus, and cannot update other rows.

So I suppose the idea to "evaluate one condition and update 2+ rows" is not achievable by the tool.

But the below formula may work for your purpose.

IF [Field1] = 1000
OR [Row-1:Field1] = 1000
OR [Row-2:Field1] = 1000
THEN 1000 / 3
ELSE [Field1]
ENDIF

Result looks like below:

Amilley

You are right there, Multi-row formula tool sounds like the right answer.

Remember, even though the tool's name is 'multi-row' formula, the technology is still performing an action each row one at a time, and taking an action one at a time. The concept of multi-row, is the tool can evaluate conditions from lines preceding or after the current line being actioned on.

I think your solution is right there but rather than using Row+ and looking forward, see if you can tweak you logic to look backward:

if [field1] = 1000 or [row - 1 field 1] = 1000 or [row-2 field 1] = 1000

then [field1]/3

as long as the logic above (using the OR condition) would work, it will solve your example you provided. Let me illustrate:

Field1

1000

400

675

The first step of the evaluation would be on 1000 and the condition is met (because [field 1] ==1000)

The next step of the evaluation would be on 400 and the condition is met (because [Row -1 field 1] ==1000)

So on and so forth

Lastly, in order for this to work, set the configuration option to "create new field" vs "update existing field" this way the evaluation criteria doesn't decay as the tool evaluates each record.