Forum Discussion
Solved
CONCATENATEX with Multiple Columns
Hi everyone,
Is there a way to take advantage of CONCATENATEX's delimiter feature (specifically: add a comma when there's another item in the list, don't add a comma for the last or only item) across a ROW vs. down a COLUMN?
I need to list blank fields as follows:
Source:
| ID | Field1 | Field1Valid | Field2 |
| ABC | 123 | N | |
| DEF | Y | 123 | |
| GHI | 123 | N | 123 |
| JKL | N | ||
| MNO | 123 | N | |
| PQR | N | 123 | |
| STU | 123 | N | 123 |
| VWX | Y |
Desired outcome (Field 1 is considered valid for either not being blank OR for Field1Valid = "Y"):
| ID | MissingFields |
| ABC | Field2 |
| DEF | |
| GHI | |
| JKL | Field1, Field2 |
| MNO | Field2 |
| PQR | Field1 |
| STU | |
| VWX | Field2 |
So far I've tried:
MissingField(s) =
VAR _F1 =
IF( 'Sample'[Field1] <> BLANK() || 'Sample'[Field1Valid] = "Y",
BLANK(),
"Field1" )
VAR _F2 =
IF( 'Sample'[Field2] <> BLANK(),
BLANK(),
"Field2" )
RETURN
_F1 & ", " & _F2MissingField(s) 2 =
VAR _F1 =
IF( 'Sample'[Field1] <> BLANK() || 'Sample'[Field1Valid] = "Y",
BLANK(),
"Field1, " )
VAR _F2 =
IF( 'Sample'[Field2] <> BLANK(),
BLANK(),
"Field2" )
RETURN
CONCATENATEX( CALCULATETABLE( 'Sample', ALLEXCEPT( 'Sample', 'Sample'[ID] ) ), _F1 & _F2 )I am close but I need help with the commas:
Thank you in advance!
BekahLoSurdo add condition in following
MissingField(s) = VAR _F1 = IF( 'Sample'[Field1] <> BLANK() || 'Sample'[Field1Valid] = "Y", BLANK(), "Field1" ) VAR _F2 = IF( 'Sample'[Field2] <> BLANK(), BLANK(), "Field2" ) RETURN SWITCH( TRUE(), __F1 == BLANK() && __F2 == BLANK(), "", __F2 == BLANK(), __F1, __F1 == BLANK(), __F2, _F1 & ", " & _F2 )
1 Reply
BekahLoSurdo add condition in following
MissingField(s) = VAR _F1 = IF( 'Sample'[Field1] <> BLANK() || 'Sample'[Field1Valid] = "Y", BLANK(), "Field1" ) VAR _F2 = IF( 'Sample'[Field2] <> BLANK(), BLANK(), "Field2" ) RETURN SWITCH( TRUE(), __F1 == BLANK() && __F2 == BLANK(), "", __F2 == BLANK(), __F1, __F1 == BLANK(), __F2, _F1 & ", " & _F2 )