Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kpham
Resolver I
Resolver I

IF statement to select a value of two rows

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.

 

Kpham_0-1653587568008.png

Kpham_1-1653587697520.png

 



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



 

1 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 

View solution in original post

4 REPLIES 4
tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

Kpham_0-1653589320063.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

 

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.