If condition with special characters in column not working using DAX

[bhaskarpbi9999]

Hi ,

i am using below if condition and getting error as syntax with special characters is not working.

Exclu Name = If((AccountParent[Customer Name] in ("AMAZON FC ETZ2 HZ",
"TERRA SOLUTIONS GMBH",
"GB MACHINES GMBH & CO KG",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"MAIL BOXES ETC",
"K+G WETTER GMBH",
"AK KUNSTSTOFFSPR GMBH&CO KG",
"SERVICECOOPERATIONHENSELI GMBH",
"BEHAWE NATURPRODUKTE",
"CORTECO GMBH",
"IBK WIESEHAHN GMBH",
"Q-RAILING CENTRAL EUROPE GMBH",
"TRIBO HARTSTOFFE GMBH",
"BTS EUROPA AG",
"DB LNFRAGO",
"COMACS GMBH",
"ARGUS DATA INSIGHTS SERVICE GMBH",
"PAN DACOM NETWORKING AG",
"IN TOUCH GMBH",
"MAIL BOXES ETC 0056",
"B&G MASCHINENHANDELSGESELLSCHAFT MBH",
"LOGITRANS GMBH",
"LOGITRANS GMBH",
"WIELAND ELECTRIC GMBH",
"GSL IN MITTELDEUTSCHLAND MBH",
"FOSSIL EUROPE GMBH",
"CLAAS MAIN DONAU GMBH & CO KG",
"LETMEREPAIR RHEIN-MAIN GMBH",
"WEINHANDLUNG BIANCO E ROSSA",
"WOLF & PABICH GMBH",
"CAM GMBH & CO KG",
"BREMER SPIRITOUSEN CONTOR GMBH",
"PANTERA PRODUCT GMBH",
"VOLZ GRUPPE GMBH",
"H & O DIENSTLEISTUNGEN GMBH",
"DRYKORN MODEVERTRIEBS GMBH&CO KG",
"HONSEL DISTRIBUTION GMBH & CO",
"MERZ DENTAL GMBH",
"NEUROCARE GROUP AG",
"RSM EBNER STOLZ PARTNERSCHAFT MBB",
"PIONEER SERVICE WERKSTATT",
"BAERHAUSEN GMBH & CO KG",
"BYTECLUB GMBH",
"PINGUINDRUCK GMBH",
"WILLIG TRANSPORTLOGISTIK",
"PICO FOOD GMBH",
"SCHENKER DEUTSCHLAND AG",
"HHS ZERSPANNUNGSWERKZEUG",
"GIZEH RAUCHERBEDARF GMBH",
"ARVATO SE",
"NOER A GERSDORF/M STREUBEL GBR",
"MAYER DACHKERAMIK GMBH",
"DQ-PP SP Z O O ZN",
"PICARD&WIELPUETZ GMBH&CO KG",
"SM SCHLOSS & SCHLUESSEL GMBH",
"MYKITA SHOP GMBH",
"GIMEX DR GAO IM-EXPORT GMBH",
"AFH GERMANY GMBH",
"NIKA OPTICS GMBH",
"VAMEDIS DEUTSCHLAND GMBH",
"METZGEREI CLAUS BOEBEL",
"CHACO GMBH",
"ALCAR DEUTSCHLAND GMBH",
"SEHESTEDTER NATURFARBEN HANDEL GMBH",
"SCHENKER DEUTSCHLAND AG",
"BODE COMPONENTS GMBH",
"TOD-S DEUTSCHLAND GMBH",
"YELLOTOOLS GMBH",
"EIBACH GMBH",
"PINA PARIE-NAIL COSM LINE GMBH",
"FEINE WEINE",
"WALTER ZEPF SCHMIERUNGSTECHNIK",
"DELKER OPTIK GMBH",
"DREISSIGACKER WEIN GMBH",
"TECVIA GMBH",
"ATHMER OHG",
"KANZLEI DR STOELZEL",
"4BUSINESS WERBE & VERTR GMBH",
"EOS DTS INKASSO-DIENST GMBH",
"RT-REISEBUERO GMBH",
"BMI DEUTSCHLAND GMBH",
"TOP LIGHT GMBH & CO KG",
"STOCKMEIER FOOD GMBH",
"DBT GMBH",
"I M S WORKWEAR GMBH",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"AFH GERMANY GMBH",
"DER WEINHANDEL BUERGERHEIM GMBH",
"ART2 KUNSTRAUM",
"HANDY CHILLEN",
"PN DISTRIBUTION GMBH",
"RISTORANTE VINOTECA ROMANTICA",
"STEFAN HAD",
"HDE LOGISTIK ZENTRUM",
"FOBOXY GMBH",
"PACKOA GMBH",
"MAIL BOXES ETC 2526",
"FIT TECHNOLOGY GMBH",
"SOLVIUM CAPITAL VERTRIEBS GMBH",
"RETURNGO",
"DELOITTE GMBH WIRTSCHAFTSPRUEFUNGSGESELLSCHAFT",
"DRAEGER SAFETY AG & CO KGAA",
"AFH GERMANY GMBH",
"ISR GMBH",
"WEINHANDEL HIERGEIST INH M STECHER",
"SPS GERMANY GMBH",
"LEXMARK DEUTSCHLAND GMBH",
"INDUSTRIE24 GMBH",
"SCHENKER DEUTSCHLAND AG",
"LUXOTTICA FASHION BRILLEN",
"AMAZON LOGISTIK GMBH",
"RHENUS MAILROOM SERVICES GMBH",
"STRAHL SCHLIESSTECHNIK GMBH",
"KIOSKE GMBH",
"GAUGELE GMBH",
"BERGER BAU INDUSTRIEBEDARF GMBH",
"JIL SANDER S P A NIEDERLASSUNG DEUTSCHLAND",
"HEISE MINDFACTORY GMBH",
"LOGITRANS GMBH",
"AGRAVIS TECHNIK MUENSTERLAND EMS GMBH",
"EAM UG",
"HUELYA TUERK KFZ HANDEL",
"EXTOX GASMESS-SYSTEME GMBH",
"H M WITT & CO GMBH",
"GEIGER PERFORMANCE GMBH",
"LUEFTUNGSGITTER24 P BLESSMANN",
"HAHN SPORTWAGEN PFORZHEIM GMBH",
"DANIELLE MAZZEO",
"KBMS LOGISTIK GMBH",
"GLOBAL ACCESSORIES TRADING GMB",
"RF SICHERHEIT",
"WEININGER",
"WEINHAUS MESSMER KG",
"HEIKO SCHULLER",
"ERTUGRUL KUECUEKKAYA",
"GRAL SYSTEME GMBH",
"WEINKELLEREI HOHENLOHE EG",
"WEINKELLEREI HOHENLOHE EG",
"U TRUMP-BERNDT GBR",
"WEINGUT WENDEL GBR",
"WEINBERG",
"IMPULSE DYNAMICS GERMANY GMBH",
"WEINHAUS BETTINA SCHUMANN UG (HB)",
"GEISBERGER GESELLSCHAFT FUER ENERGIEOPTIMIERUN MBH",
"RHEINLANDBOOTE GMBH",
"SALEMASTERS",
"BIERMANUFAKTUR ENGEL GMBH & CO KG",
"ROT WEISS ROSE",
"LUXUS LOVES LINGERIE",
"ZIPSE GMBH & CO KG",
"FLUID SYSTEMS & AUTOMATION GMBH",
"KANICKE BUSINESS SERVICE GMBH",
"SCHENKER DEUTSCHLAND AG",
"MAYA KAFFEE SEIT 1991",
"STERNAUTO GMBH",
"COMSPOT REPAIR GMBH",
"EUROPAKET+ GMBH",
"TEAMPLAY1 GMBH",
"ZEICHENTECHNIK ROSENBAUM GMBH & CO KG",
"RUNDOOR TUERAUTOMATIK GMBH & CO KG",
"DSV ROAD GMBH",
"SCHENKER & CO AG",
"BLUELEON E K",
"DSV STUTTGART GMBH & CO KG",
"HOYA SURGICAL OPTICS EDC",
"FRESENIUS KABI MEDTECH SERVICES GMBH",
"ELRIED MARKIERUNGSSYSTEME GMBH",
"AGROLAGER INH RENATE BAUER",
"CONCAR POWER TRANSMISSION SYS",
"DRUCK UND DISPLAY GMBH",
"KNOPF UND KNOPF GMBH + CO KG",
"LOGITRANS GMBH",
"SGS INSTITUT FRESENIUS GMBH",
"MAGNOS TURBOSYSTEMS GMBH",
"TSL TEXTIL-SERVICE & LOGISTIK GMBH",
"CHRISTOF FISCHER GMBH",
"AGENTUR-NETWORK MARKETING",
"GRAPHITE-SOLUTIONS GMBH",
"BSW BUSINESS SERVICE GMBH MBE3133",
"HS MOTORSPORT ENTWICKLUNG & VERTRIEBSGESELLS GMBH",
"ELECTRA GMBH",
"DSV ROAD B V",
"UNZER POS GMBH",
"PREGEL GERMANY",
"SPS GERMANY GMBH DOCUMENT LOGISTIC SERVICES",
"DACHSER SE",
"RHIEM SERVICES GMBH",
"RHIEM SERVICES GMBH",
"SCHLEICH GMBH",
"KIOTEC GMBH",
"MARXEN WEIN GMBH & CO KG",
"CHRISTOF FISCHER GMBH",
"NOVENTI HEALTH SE",
"NOVENTI HEALTH SE",
"SCHMIDT KAFFEEMASCHINEN GMBH",
"OLIVER ZETER GBR",
"TELEPERFORMANCE GERMANY",
"JEHNERT SOUND DESIGN",
"HEROAL GMBH & CO KG",
"SELIMED GMBH",
"LUMESCA GMBH",
"PEBRO GMBH",
"PHILIPS GMBH UB COUNTRY ORGANIS",
"EVOQUA WATER TECHNOLOGIES GMBH",
"AUMUELLER AUMATIC GMBH",
"NET-XPRESS GMBH & CO KG",
"B R PRODUCTIONS&PACKAGING GMBH",
"OPTIKER BODE GMBH",
"KROSCHKE GMBH",
"DRUCKEREIERZEUGNISSE",
"COMPUTER & MODELLBAHN F HUEBEL",
"NIPPON EXPRESS DEUTSCHL GMBH",
"STRYKER GMBH",
"LILLYS WEINSHOP",
"MARRECK GMBH",
"SEEITNOW GMBH",
"FRAU ROTTMAN",
"PRODUO DEUTSCHLAND GMBH",
"CARDS-X GMBH",
"AETZWERK GMBH",
"RT REPROTECHN DE 140",
"LUKAS-ERZETT GMBH & CO KG",
"WEINDEPOT HANS PETER GIETZEN",
"INEXOGY SMART METERING GMBH & CO KG",
"GEILE WARENAUTOMATEN GMBH",
"CB WEINHANDEL",
"DIE SCHAFTMACHER GMBH",
"A HARTRODT DEUTSCHLAND (GMBH & CO) KG",
"A HARTRODT DEUTSCHLAND (GMBH & CO) KG",
"NOBLETEXTILES ANDREAS HAUER",
"ATLAS DIAMANT GMBH",
"PAUL KRYWALSKY",
"PROMOWOLSCH GMBH & CO KG",
"LOVELY PIE",
"WAREMA SONNENSCHUTZTECHNIK GMBH",
"MERCEDES BENZ AG LN 12105979",
"PINSKER DRUCK UND MEDIEN GMBH",
"DCS DIGITAL CINEMA SERVICES GMBH",
"KIESEL TRADE GMBH & CO KG",
"MERCEDES-BENZ AG NDL BERLIN LN 12105979",
"WEDIY",
"WEINCONNECT",
"PORSCHE LIFESTYLE GMBH & CO KG",
"TCHIBO GMBH KREDITOREN",
"EUGEN RISS - GOBOPRO",
"HR DIREKTVERKAUF GMBH",
"DSV ROAD GMBH",
"JAKOB&EVA GMBH & CO KG",
"FISCHER LANDTECHNIK GMBH",
"BRUNELLO CUCINELLI RETAIL",
"TSM GMBH",
"LOUIS VUITTON DEUTSCHLAND GMBH",
"BIRD HOME AUTOMATION GMBH",
"WEINHAUS C FEHSER OHG",
"DEQSTER GMBH",
"SG LEUCHTEN GMBH",
"GOODSTUFF FASHION COMPANY GMBH",
"FIRMA BERG / VIVAVIS",
"ORTHOPEDIE WALTER",
"VOLK LANDTECHNIK GMBH",
"WEINPARADISO",
"AUTOLACK BURMEISTER GMBH",
"OROVIVO FILIALE O030 MUENCHEN RIEM",
"DERMAMEDIX",
"ACTEMIUM MECHATRONIC GMBH",
"CTD GERMANY E K",
"MEMAS DESSERT",
"MYSALVORA",
"DSV AIR & SEA GERMANY GMBH",
"VELASCA GMBH",
"DMG MORI DIGITAL GMBH",
"HUNTER DOUGLAS C/O UPS",
"WEINGUT JOACHIM SCHUMACHER",
"LETMEREPAIR RHEIN-MAIN GMBH",
"DEFLEX DICHTSYSTEME GMBH",
"TWENTYFIVECERAMICS",
"DSV AIR & SEA GERMANY GMBH",
"LOGITRANS GMBH",
"LOGITRANS GMBH",
"CRAIG THORROLD",
"COEMERT BONCUK",
"HERBERT KREML",
"CROGUSTO",
"WEINGUT PRINZ SALM FELIX PRINZ ZU SALM-SALM",
"DACHSER SE AIR & SEA LOGISTICS",
"VOLKSWAGEN GROUP OTLG MBH",
"RAD CENTER DERKUM GMBH",
"WILHELM FRICKE SE"
)),"","Exclude WSPL")

Could you please help me with the above formula. Many thanks in Advance.

Regards,

Bhaskar

[Shahid12523]

Fix: Create a separate table (ExclusionList) with all customer names and use:

Exclu Name =
IF (
CONTAINS ( ExclusionList, ExclusionList[Customer Name], AccountParent[Customer Name] ),
"Exclude WSPL",
""
)

This avoids syntax errors and is easier to maintain.

Want me to show you a quick way to bulk load your list into a table?

[bhaskarpbi9999]

Hi Shahid12523,

Thnaks and kudos for your support.

Regards,

Bhaskar

[v-menakakota]

Hi @bhaskarpbi9999  , 

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @Shahid12523 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

Creating a dedicated ExclusionList table and using CONTAINS. This is the most reliable and scalable approach. It avoids syntax errors with special characters and keeps the logic much easier to maintain if the list of excluded customers changes in the future.

This way you won’t run into special character issues, and updating the list is as simple as adding or removing rows from the ExclusionList table.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team

[bhaskarpbi9999]

Hi ,

Thanks and kudos for your support

Regards,

Bhaskar

[AmiraBedh · Accepted solution]

Hello !

Your issue is about how the identifier is written, not the values in your list.

The AccountParent should be 'AccountParent'.

Also for a better readbility, put the names in a small table 

Exclu Name =
IF (
CONTAINS ( 'ExclusionList', 'ExclusionList'[Customer Name]; 'AccountParent'[Customer Name] ),
BLANK(),
"Exclude WSPL"
)

or you can use DATATABLE or a variable :

Exclu Name =
VAR ExclusionList =
DATATABLE (
"Customer Name", STRING,
{
{ "AMAZON FC ETZ2 HZ" },
{ "TERRA SOLUTIONS GMBH" },
{ "WILHELM FRICKE SE" }
}
)
RETURN
IF (
'AccountParent'[Customer Name] IN ExclusionList,
BLANK(),
"Exclude WSPL"
)

[bhaskarpbi9999]

Hi AmiraBedh

Thanks fand kudos  for your suport.the formula works fine and i am acccepting it as solution

Regards,

Bhaskar

Preview

[vojtechsima]

Hey man, @bhaskarpbi9999 ,

Just swap the parentheses with curly brackets, IN is essentially a table of one column, and tables are constructed with {}.

so just do

Exclu Name = IF(
AccountParent[Customer Name] in {"AMAZON FC ETZ2 HZ", "TERRA SOLUTIONS GMBH", "GB MACHINES GMBH & CO KG"..},"","Exclude WSPL")

[bhaskarpbi9999]

Hi ojtechsima,

Thanks and kudos  for your suport

[bhaskarpbi9999]

Hi vojtechsima,

Thanks for your support.

I have tried with your suggested formula but it is working due to to special characters in customer name column which is giving below eroor.

Error Details : To use special characters in a column name, enclose the entire name in brackets ( [] ) and add a ] to any closing brackets in the name.

Could you please assist me the solution for above screnario with special characters.

Regards,

Bhaskar

 

[vojtechsima]

@bhaskarpbi9999  all right, got it now.

If you have special character, you need to escape it with "]", given this would be your column:

AccountParent[Customer Name]

Where AccountParent is not table but still part of column name, you do this:

[AccountParent[Customer Name]]] 

The full syntax with table would then look like this:

'Table'[AccountParent[Customer Name]]]