Conditional Formula different fields.

[romovaro]

Hi

I need your help regarding one formula. 

I have the table below.

THe rule is

If the same Project manager/Coordinator is managing both CUIDs (Normal one and the same one ending with "i") then, column Bothmanager1pm = Yes, otherwise No

Project Scope can vary but if contains strings "WFN" or "FNW" then Yes. (THe issue is that only the rows with the CUIDs  ending with i contains than info

Name	Project Manager/Coordinator	Project Scope	CUID	CUID_i	BothManager1PM	Include WFN or iHCM	Scope for FastTrack
Client A	Pau Gasol		ACOUIL1	ACOUIL1	Yes	No	NO	Currently with my formula, the field is "no" and I also want to add the "yes"
Client A	Pau Gasol	…WFN…	ACOUIL1i	ACOUIL1	Yes	Yes	Yes
Client B	Pau Gasol		LAX	LAX	No	No	No
Client B	Lebron James	…WFN…	LAXi	LAX	No	Yes	No

then I have the formula:

Scope FastTrack = IF('PV Cel Report'[BothManaged1PM]="Yes"&&'PV Cel Report'[Include WFN or iHCM]="Yes", "In Scope for Fast Track", "2-cycle")

THe problem is that I also want to include the other CUID (the one NOT ending with "i") in the conditional formula and I am struggling to add that one also in the dax formula.

Any help?

thanks

[romovaro]

thanks. It works 🙂

[v-tangjie-msft · Accepted solution]

Hi @romovaro ,

According to your description, here are my steps you can follow as a solution.

(1)  My test data is the same as yours.

(2) We can create a  calculated column.

Scope FastTrack = 
 var _flag=ADDCOLUMNS('PV Cel Report',"flag",IF('PV Cel Report'[BothManager1PM]="Yes"&&'PV Cel Report'[Include WFN or iHCM]="Yes",1, 0))
 var _sumflag=SUMX(FILTER(_flag,[Name]=EARLIER('PV Cel Report'[Name])&&[Project Manager/Coordinator]=EARLIER('PV Cel Report'[Project Manager/Coordinator])),[flag])
 return IF(_sumflag=1,"Yes","No")

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.