Populate column based on value in another column using DAX

[ClemFandango]

Hi all,

I am trying to create a calculated column that returns “yes” where any ‘Ref’ value has one or more values in ‘Code’. The data table looks like below and both ‘Ref’ and ‘Code’ are text columns. I am trying to recreate ‘RefHasCode?’ as a new column in DAX that returns either “Yes” or blank based on conditions in both 'Ref' & 'Code'.

Any ideas greatly appreciated!

Table1

Ref	Code	RefHasCode?
1	233	Yes
1	34	Yes
2
3		Yes
3	12	Yes
1		Yes

[Bmejia · Accepted solution]

Add this column to your table

RefHasCode? =

 VAR CountCode = CALCULATE(COUNT('Table1'[Code]),ALLEXCEPT('Table1','Table1)'[Ref]))

 RETURN

If(CountCode>=1,"Yes"," ")

Note that when i pasted your data into my data sample the blanks were being counted, so i cleaned the records on my table by replacing the blanks with "null" during the data transformation.  You might not run into this issue but just in case you do replace your blank values in the Code column to null.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = if(CALCULATE(COUNTa(Data[Code]),FILTER(Data,Data[Ref]=EARLIER(Data[Ref])&&Data[Code]<>BLANK()))>0,"Yes",BLANK())

Hope this helps.

[ClemFandango]

Huge thanks @Ashish_Mathur  & @Bmejia !

Both of your suggestions worked wonderfully!

[Ashish_Mathur]

You are welcome.

[Bmejia · Accepted solution]

Add this column to your table

RefHasCode? =

 VAR CountCode = CALCULATE(COUNT('Table1'[Code]),ALLEXCEPT('Table1','Table1)'[Ref]))

 RETURN

If(CountCode>=1,"Yes"," ")

Note that when i pasted your data into my data sample the blanks were being counted, so i cleaned the records on my table by replacing the blanks with "null" during the data transformation.  You might not run into this issue but just in case you do replace your blank values in the Code column to null.