Hello I have the Below table: NameProject Manager / CoordinatorImplementation ConsultantRegional PMCountry Long NameCIDCUIDClient ADorottya Fernabdez FranceNCPFNCPFFR01Client BJohn SMith John SMithFranceASPNASPNFR01Client BJohn SMith John SMithGermanyASPNASPNDE01Client BJohn SMith John SMithKorea, Republic ofASPNASPNKR01Client BJohn SMith John SMithMalaysiaASPNASPNMY01Client BJohn SMith RussiaASPNASPNRU01Client B SpainASPNASPNES01Client BJohn SMith Ryan HughesChinaASPNASPNCN01Client BJohn SMith ChinaASPNASPNCN02Client BJohn SMith IndiaASPNASPNIN01Client BJohn SMith Ryan HughesItalyASPNASPNIT01Client BJohn SMith JapanASPNASPNJP01Client BJohn SMith SingaporeASPNASPNSG01Client BJodi Forester Dorottya FernabdezBelgiumASPNASPNBE02Client CJodi Forester Oman10000622158Client CJodi Forester Poland10000632159Client CJodi Forester Saudi Arabia10000642161Client CJodi Forester South Africa10000652163Client CJohn SMith Sweden10000662164Client CJodi Forester United Arab Emirates10000672167Client D ThailandTNSGTNSGTH01 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

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,LiangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

romovaro

Responsive Resident

4 years ago

Solved

Dax formula comparing 2 columns

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

Need Help

V-lianl-msft

4 years ago

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.

Dax formula comparing 2 columns.pbix34 KB

Forum Discussion

Dax formula comparing 2 columns

3 Replies

Related content

If formula

formula

DAX Formula Help

Compare Dynamic Columns in a Matrix

DAX Formula for subtracting columns?