The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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"
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Thanks a lot, but how can I use the switch function to replace nested IF?
@Marcox28 , You switch and Use OR
example
Switch(True(),
containsstraing('FactTable'[Code],"[CT]") || containsstraing('FactTable'[Code],"[CA]" ),"GPS",
//Add others
)
Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56
CONTAINSSTRING and CONTAINSSTRINGEXACT: https://www.youtube.com/watch?v=XbgLGDvWdWQ&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=44