Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need Help with Dax switch and containsstrings

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
containsstrings
DAX
Need Help
SWITH
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      This helped me a lot!

  • MasterSonic's avatar
    MasterSonic
    Helper IV
    3 years ago

    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é"

        )