Forum Discussion
ClemFandango
Advocate II
Advocate IIPopulate column based on value in another column using DAX
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 |
Add this column to your table
RefHasCode? =VAR CountCode = CALCULATE(COUNT('Table1'[Code]),ALLEXCEPT('Table1','Table1)'[Ref]))RETURNIf(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.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.
4 Replies
BmejiaSuper User
Add this column to your table
RefHasCode? =VAR CountCode = CALCULATE(COUNT('Table1'[Code]),ALLEXCEPT('Table1','Table1)'[Ref]))RETURNIf(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
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
- Ashish_Mathur
You are welcome.