Dax formula comparing 2 columns

[romovaro]

Hello

I have the Below table:

Name	Project Manager / Coordinator	Implementation Consultant	Regional PM	Country Long Name	CID	CUID
Client A	Dorottya Fernabdez			France	NCPF	NCPFFR01
Client B	John SMith		John SMith	France	ASPN	ASPNFR01
Client B	John SMith		John SMith	Germany	ASPN	ASPNDE01
Client B	John SMith		John SMith	Korea, Republic of	ASPN	ASPNKR01
Client B	John SMith		John SMith	Malaysia	ASPN	ASPNMY01
Client B	John SMith			Russia	ASPN	ASPNRU01
Client B				Spain	ASPN	ASPNES01
Client B	John SMith		Ryan Hughes	China	ASPN	ASPNCN01
Client B	John SMith			China	ASPN	ASPNCN02
Client B	John SMith			India	ASPN	ASPNIN01
Client B	John SMith		Ryan Hughes	Italy	ASPN	ASPNIT01
Client B	John SMith			Japan	ASPN	ASPNJP01
Client B	John SMith			Singapore	ASPN	ASPNSG01
Client B	Jodi Forester		Dorottya Fernabdez	Belgium	ASPN	ASPNBE02
Client C	Jodi Forester			Oman	1000062	2158
Client C	Jodi Forester			Poland	1000063	2159
Client C	Jodi Forester			Saudi Arabia	1000064	2161
Client C	Jodi Forester			South Africa	1000065	2163
Client C	John SMith			Sweden	1000066	2164
Client C	Jodi Forester			United Arab Emirates	1000067	2167
Client D				Thailand	TNSG	TNSGTH01

I can have diff scenarios.

Scenario 1: Name in the  Project Manager / Coordinator column is EQUAL to Name in Regional PM column.

Scenario 2: Name in the Project Manager/Coordinator column but BLANK in the Regional PM column or viceversa. 

Scenario 3: Name in the Project Manager/Coordinator column and different Name in the Regional PM column.

Scenario 4: Name in the Regional PM column column and different Name in the Project Manager/Coordinator column.

Scenario 5: Both columns are BLANK

I would like to create custom formulas for every scenario using the CUID column (unique customer number) as filter.

Scenario 1 example: Name in the  Project Manager / Coordinator column is EQUAL to Name in Regional PM column and columns are not blank. Tried to create formula but it's not working.

Scenario 1 =

var tab =

CALCULATETABLE (

VALUES ( PVCEL[CUID]),

FILTER (

PVCEL,

PVCEL[Project Manager / Coordinator] = PVCEL[Regional PM]

)

)

return

CALCULATE (

COUNT ( PVCEL[CUID] ),

INTERSECT ( VALUES(PVCEL[CUID] ), tab ),

FILTER (

PVCEL,

&& PVCEL[Regional PM] <> BLANK ()

)

)

and the idea is to create similar formulas with the diff scenarios. Any help? thanks

[Ashish_Mathur]

Hi,

Please show your expected result.

[V-lianl-msft · Accepted solution]

Hi @romovaro ,

Hope I understand correctly. Please try to create the following measures:

S1 = CALCULATE(COUNT('Table'[CUID]),FILTER('Table','Table'[Project Manager / Coordinator]='Table'[Regional PM]&&'Table'[Project Manager / Coordinator]<>""))

S2 = CALCULATE(COUNT('Table'[CUID]),FILTER('Table','Table'[Regional PM]=""&&'Table'[Project Manager / Coordinator]<>""))

S3 = CALCULATE(COUNT('Table'[CUID]),FILTER('Table','Table'[Regional PM]<>'Table'[Project Manager / Coordinator]&&'Table'[Regional PM]<>""))

S4 the same as S3

S5 = CALCULATE(COUNT('Table'[CUID]),FILTER('Table','Table'[Regional PM]=""&&'Table'[Project Manager / Coordinator]=""))

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

[romovaro]

Awesome Liang. It works. Thank you so much