Need Help with Dax switch and containsstrings

[Anonymous]

Hey guys,

I have the following Dax formula that attributes an e-mail to an e-mail type, but I wonder if I could rewrite it better:

Type E-mail =

SWITCH(TRUE(),

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"onbekend"),"Onbekend",

'Maia Data'[maia.Persoon.e-mail] = BLANK (), "onbekend",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"sbz"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"mailstreet"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"menzis"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"ntac"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"onderling"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"agriver"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"donatus"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"ovm"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"zlm"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"cz.nl"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"dsw.nl"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"zorgenzekerheid"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"gezondmetsalut"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"sgv"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"jtv-zoetermeer"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"stcdeventer"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"star-shl"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"lalarsk"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"medicinfo"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"unive"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"vanderdussen"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"vecozo"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"vektis"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"vgz.nl"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"zorg"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"zn.nl"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"schuldenlab"),"Zakelijk",

CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"owmachterhoek.nl"),"Zakelijk",

"Privé")

Is there a way to combine all the 'zakelijk' attributed containsstring lines into one?

With kind regards,

Lazzanova

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can try the following column.

Create a new calculated column in 'Maia Data table

Type E-mail= var a={"sbz","mailstreet","menzis",···}
return IF(OR(CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],"onbekend"),'Maia Data'[maia.Persoon.e-mail] = BLANK ()),"onbekend",IF(COUNTROWS(FILTER(a,CONTAINSSTRING('Maia Data'[maia.Persoon.e-mail],[Value])))>0,"Zakelijk"))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This helped me a lot!

[MasterSonic]

Something like that?

Email Type = 

VAR EmailAddress = 'Maia Data'[maia.Persoon.e-mail]

RETURN 

    SWITCH(

        TRUE(),

        OR(

            CONTAINS(EmailAddress, "unive"), 

            CONTAINS(EmailAddress, "vanderdussen"),

            CONTAINS(EmailAddress, "vecozo"), 

            CONTAINS(EmailAddress, "vektis"),

            CONTAINS(EmailAddress, "vgz.nl"),

            CONTAINS(EmailAddress, "zorg"),

            CONTAINS(EmailAddress, "zn.nl"), 

            CONTAINS(EmailAddress, "schuldenlab"),

            CONTAINS(EmailAddress, "owmachterhoek.nl")

        ), "Zakelijk",

        "Privé"

    )