cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Optimize formula Calculated column

Hi Guys, in one of my model I have this computed column. How can I optimize the code to improve performance?

CalculateColumn =
IF(SEARCH("[CT]",'FactTable'[Code],1,0 )>0,"GPS",
IF(SEARCH("[CA]",'FactTable'[Code],1,0 )>0,"GPS",

if(SEARCH("ESCLUS",'FactTable'[State],1,0 )>0,"ESCLUSIONI",
IF(SEARCH("E5",'FactTable'[Code],1,0 )>0,"ESCLUSIONI",

if(SEARCH("GPSPOS",'FactTable'[TSME_Proto],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M12",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M11",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M10",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M09",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M08",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M06",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M04",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M03",'FactTable'[Code],1,0 )>0,"PATTUGLIE",
IF(SEARCH("M02",'FactTable'[Code],1,0 )>0,"PATTUGLIE",

IF(SEARCH("E4",'FactTable'[Code],1,0 )>0,"COMANDI",
IF(SEARCH("E00",'FactTable'[Code],1,0 )>0,"COMANDI",
IF(SEARCH("P08",'FactTable'[Code],1,0 )>0,"COMANDI",
if(SEARCH("SPENT",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("ACCES",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("INSERITO",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("ESEGUITO",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("DISABILIT",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("ATTIVATO",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("SPENTO",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("VERIFICA",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("BLOCCAT",'FactTable'[State],1,0 )>0,"COMANDI",
if(SEARCH("VERIFICA",'FactTable'[State],1,0 )>0,"COMANDI",

IF(SEARCH("V03",'FactTable'[Code],1,0 )>0,"MANOMISSIONI",
if(SEARCH("APERTO",'FactTable'[State],1,0 )>0,"MANOMISSIONI",

if(SEARCH("E7",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P50",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("G02",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("L11",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P27",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P51",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P02",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P18",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P26",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("G03",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("G04",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P26",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P15",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P16",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P06",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P05",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("P10",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("AA",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("Y",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("E3",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("V04",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("P25",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("P16",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("P15",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("LT",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("G09",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("G07",'FactTable'[Code],1,0 )>0,"ANOMALIE",
IF(SEARCH("G06",'FactTable'[Code],1,0 )>0,"ANOMALIE",
if(SEARCH("ASSENTE",'FactTable'[State],1,0 )>0,"ANOMALIE",
if(SEARCH("Fuori Servizio",'FactTable'[State],1,0 )>0,"ANOMALIE",
if(SEARCH("SCARICA",'FactTable'[State],1,0 )>0,"ANOMALIE",

if(SEARCH("ITEM",'FactTable'[TSME_Proto],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("JPEG",'FactTable'[TSME_Proto],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("INFO",'FactTable'[TSME_Proto],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("TEXT",'FactTable'[TSME_Proto],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("TAG",'FactTable'[TSME_Proto],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("P12",'FactTable'[Code],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("P56",'FactTable'[Code],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("G01",'FactTable'[Code],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("P56",'FactTable'[Code],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("V01",'FactTable'[Code],1,0 )>0,"SEGNALAZIONI",
IF(SEARCH("P52",'FactTable'[Code],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("AV",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("INTERVENIRE",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("ON",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("OFF",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("INIZIO",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("Normale",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",
if(SEARCH("FINE",'FactTable'[State],1,0 )>0,"SEGNALAZIONI",

IF(SEARCH("R",'FactTable'[Code],1,0 )>0,"RIPRISTINI",
IF(SEARCH("M07",'FactTable'[Code],1,0 )>0,"RIPRISTINI",
if(SEARCH("CARICA",'FactTable'[State],1,0 )>0,"RIPRISTINI",
if(SEARCH("Chiuso",'FactTable'[State],1,0 )>0,"RIPRISTINI",
if(SEARCH("OK",'FactTable'[State],1,0 )>0,"RIPRISTINI",
if(SEARCH("PRESENTE",'FactTable'[State],1,0 )>0,"RIPRISTINI",
if(SEARCH("In Servizio",'FactTable'[State],1,0 )>0,"RIPRISTINI",
if(SEARCH("RIPRISTINO",'FactTable'[State],1,0 )>0,"RIPRISTINI",

IF(SEARCH("V06",'FactTable'[Code],1,0 )>0,"ALLARMI",
IF(SEARCH("E1",'FactTable'[Code],1,0 )>0,"ALLARMI",
IF(SEARCH("E2",'FactTable'[Code],1,0 )>0,"ALLARMI",
IF(SEARCH("V05",'FactTable'[Code],1,0 )>0,"ALLARMI",
if(SEARCH("V09",'FactTable'[Code],1,0 )>0,"ALLARMI",
if(SEARCH("V08",'FactTable'[Code],1,0 )>0,"ALLARMI",
if(SEARCH("ALLARME",'FactTable'[State],1,0 )>0,"ALLARMI",

IF(SEARCH("E6",'FactTable'[Code],1,0 )>0,"PROVE AUTOMATICHE",
IF(SEARCH("P11",'FactTable'[Code],1,0 )>0,"PROVE AUTOMATICHE",

IF(SEARCH("O0",'FactTable'[Code],1,0 )>0,"STATI",
IF(SEARCH("I0",'FactTable'[Code],1,0 )>0,"STATI",
"not categorize"
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

2 REPLIES 2
Helper II

Thanks a lot, but how can I use the switch function to replace nested IF?

Super User

@Marcox28 , You switch and Use OR

example

Switch(True(),

containsstraing('FactTable'[Code],"[CT]") || containsstraing('FactTable'[Code],"[CA]" ),"GPS",

)