Optimization help needed in DAX measure

[Anonymous]

Hi,

I need help in optimizing my code in DAX measure.

It takes more that 5 mins to get the output. 

Highlight BUOM9 = 
Var Material_Data=SELECTEDVALUE(zcvrMaterial[Material group])
Var Material_Type= SELECTEDVALUE(zcvrMaterial[Material type])
Var BUOM= SELECTEDVALUE(zcvrMaterial[External Base Unit of Measure])
return
IF (

Material_Data="CUSOWN"
    && Material_Type = "ZNB1"
    && BUOM IN {"EA","ROL","SET","KIT","CV","LB","BX","PK","BT","COI","CAN","GAL","FT","SH","RM","BAG","BD","SK","PL","DR","YDSP","L"}

||Material_Data IN {"KITTRN","KITASY"}
    && Material_Type = "ZFRT"
    && BUOM IN {"EA","SET","KIT"}

||Material_Data ="KITESD"
    && Material_Type = "ZFRT"
    && BUOM ="SET"

||Material_Data IN {"LBLTHT","CSHPPK","CSHEQP","CSHACC","PKGEQW","PKGEQP","EPSPRT","STREQW","STRTOO","TAPEQW","TAPDSP","LBLEQW","LBLADH","BAGEQP","CORBOX"}
   && Material_Type = "ZNBW"
   && BUOM ="EA"

||Material_Data ="EX-SERVIC"
    && Material_Type = "ZDIN"
    && BUOM IN {"EA","CV","LB","SK","FT2","HR"}

||Material_Data IN {"EX-RENTAL","EX-PRINTE"}
    && Material_Type = "ZDIN"
    && BUOM IN {"EA","BX"}

||Material_Data IN {"EX-PRINTE","PKGEQW","PKGEQP","STREQW","TAPEQW","LBLEQW","CSHPPK"}
    && Material_Type = "ZDIN"
    && BUOM IN {"EA"}
|| zcvrMaterial[Highlight BUOM1]=2
||zcvrMaterial[Highlight BUOM2]=2
||zcvrMaterial[Highlight BUOM3]=2
||zcvrMaterial[Highlight BUOM4]=2
||zcvrMaterial[Highlight BUOM5]=2
||zcvrMaterial[Highlight BUOM6]=2
||zcvrMaterial[Highlight BUOM7]=2
||zcvrMaterial[Highlight BUOM8]=2
,2,1)

I have tried to break the code in Highligt BUOM1 thru Highlight BUOM8. ( so ultimately it has a similar code in fulfilling other criteria)

Any help is greatly appreaciate!

Thanks,

Tejaswi

@ImkeF  @Zubair_Muhammad @Ashish_Mathur @Greg_Deckler @MFelix @MattAllington @Cmcmahan

[SteveCampbell · Accepted solution]

I would suggest you push this to Power Query (if you use import). In the "Add Column" tab you can add a custom column. The syntax is as follows:

if

zcvrMaterial[External Base Unit of Measure] = "ZNB1"
and zcvrMaterial[Material type] ="CUSOWN"
and List.Contains({"EA","ROL","SET","KIT","CV","LB","BX","PK","BT","COI","CAN","GAL","FT","SH","RM","BAG","BD","SK","PL","DR","YDSP","L"}, zcvrMaterial[External Base Unit of Measure]) 

or

List.Contains({"KITTRN","KITASY"}, zcvrMaterial[Material type]) ....... etc .....

then 2 else 1

Notice the syntax change:

if becomes if <condition> then <action1> else <action2> 

The && becomes and

The || becomes or

and the in function becoms List.Contains( { <list> }, <item> )

Remember to match the correct case (lowercase mostly)

[Anonymous]

Hi @SteveCampbell ,

Thanks for your reply!

I don't have access to edit queries. so wondering if we can achieve this with a measure.

Do you think using variable for each of this will still work in measure? 

Appreciate your reply!

Thanks,

Tejaswi