IF statement to select a value of two rows

[Kpham]

Dear Community,

I want to compare the actual and plan value of an item and based on the comparison I choose the highest value. THe outcome of the example below should be 150.

Estimated Engineering Costs = SUMX(VALUES('PS ProjectResults'),


                    IF([ETC ENG Flag]="X",
                                [ACT Engineering Costs],
                                IF([ACT Engineering Costs]<[Plan Engineering Costs ],
                                    [Plan Engineering Costs ],
                                    [ACT Engineering Costs])))

[tackytechtom]

Hi @Kpham ,

You'd like to display either the value of the measure [Plan Engineering Costs ] or the value of the measure [ACT Engineering Costs], depending which of them is higher, correct?

Have you tried to just use the if statement of your measure [Estimated Engineering Costs]?

Estimated Engineering Costs = 
IF ( [ACT Engineering Costs] < [Plan Engineering Costs ], [Plan Engineering Costs ], [ACT Engineering Costs] )

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Kpham]

that gives the right result. However the other scenario is not covered yet, but I guess /hope you know how to add this:

[tackytechtom]

Hi @Kpham ,

What is the other scenario? 🙂

I reckon it has to do with the [ETC ENG Flag]... I give it a random shot. Is it this?

IF ( 
    [ETC ENG Flag] = "X", 
    [ACT Engineering Costs],
    IF ( 
        [ACT Engineering Costs] < [Plan Engineering Costs ],
        [Plan Engineering Costs ],
        [ACT Engineering Costs]
    ),
    BLANK()
)

If not, please specify your requirement 🙂

Thanks!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Kpham · Accepted solution]

Estimated Engineering Costs = SUMX(VALUES('PS ProjectResults'),


                    IF([ETC ENG Flag]="X",
                                [ACT Engineering Costs],//[Plan Engineering Costs ]))
                                IF([ACT Engineering Costs]<[Plan Engineering Costs ],
                                  IF ( [ACT Engineering Costs] < [Plan Engineering Costs ], [Plan Engineering Costs ], [ACT Engineering Costs] ),
                                   BLANK())))

when i'm combinening the logic it's working. For sure not the best syntax but it's getting the job done. Thanks alot