Forum Discussion

balar2007's avatar
balar2007
Regular Visitor
5 years ago

SWITCH or IF

I need to transform a case statement which i have written in spotfire to Power Bi

case
WHEN [Formula]~="EBOB" THEN "MOGAS"
WHEN [Formula]~="G01" THEN "GASOIL"
WHEN [Formula]~="G50" THEN "GASOIL"

WHEN [Grade]~="DAOWA" THEN "FO"

WHEN ([Formula]~="BOB10ARGM") And ([Grade]~="M5859") THEN "MOGAS"
WHEN ([Formula]~="FRONTLINEICELS") And ([Grade]~="HYWAX") THEN "FO"

WHEN ([Client]~="MFPERNIS") And ([Formula]~="BIPEB") THEN "HEDGES"

ELSE [Formula]

 

Since multiple columns from a table is involved how do I write a DAX expression using SWITCH.  If not Switch what is the better way

Help me out

Need Help

7 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Switch is the way to go.  Here is an example with your code, but not clear if you are doing a column or measure.  This assumes [Formula] is a measure.  Note the Formula is first calculated in a variable so it is not recalculated multiple times.

     

    Switch Example =
    VAR vFormula = [Formula]
    RETURN
        SWITCH (
            vFormula,
            "EBOB", "MOGAS",
            "G01", "GASOIL",
            "G50", "GASOIL",
            vFormula
        )

     

    Regards,

    Pat

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    balar2007 , Switch True will help in this case

     

    Switch( True() ,
    , [Formula]="EBOB" , "MOGAS"
    , [Formula]="G01" , "GASOIL"
    , [Formula]="G50" , "GASOIL"

    , [Grade]="DAOWA" , "FO"

    , ([Formula]="BOB10ARGM") && ([Grade]~="M5859") , "MOGAS"
    , ([Formula]="FRONTLINEICELS") && ([Grade]~="HYWAX") , "FO"

    , ([Client]="MFPERNIS") && ([Formula]~="BIPEB") , "HEDGES"

    , [Formula]
    )

     

    refer Video: https://www.youtube.com/watch?v=gelJWktlR80

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi balar2007 ,

     

    As amitchandak  said,  Switch True is a good idea.

     

    In addititon, you can also try IF.

     

    Formula =
IF (
    [Formula] = "EBOB",
    "MOGAS",
    IF (
        [Formula] = "G01",
        "GASOIL",
        IF (
            [Formula] = "G50",
            "GASOIL",
            IF (
                [Grade] = "DAOWA",
                "FO",
                IF (
                    [Formula] = "BOB10ARGM"
                        && [Grade] = "M5859",
                    "MOGAS",
                    IF (
                        [Formula] = "FRONTLINEICELS"
                            && [Grade] = "HYWAX",
                        "FO",
                        IF ( [Client] = "MFPERNIS" && [Formula] = "BIPEB", "HEDGES", [Formula] )
                    )
                )
            )
        )
    )
)

     

     

    Best Regards,

    Stephen Tao

     

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

     

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi balar2007 

    To look for the string EBOB in [Formula] modify the line to this

    CONTAINSSTRING( [Formula], "EBOB" ), "MOGAS"

    Regards

    Phil

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi balar2007 ,

    Could you tell me if your problem has been solved?
    If it is, kindly Accept it as the solution. More people will benefit from it.
    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


    Best Regards,
    Stephen Tao