SWITCH or IF

[balar2007]

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

[v-stephen-msft]

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

[PhilipTreacy]

Hi @balar2007 

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

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

Regards

Phil

[v-stephen-msft]

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.

[amitchandak]

@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

[balar2007]

Hello Amit

Quick clarification, how do i manipulate it in case if I have to use contain in the formula you gave. For example Formula contains EBOB then equal to "MOGAS". 

[v-stephen-msft]

Hi @balar2007 ,

Please kindly refer to the below link which has similar issue:

https://community.powerbi.com/t5/Desktop/Find-if-value-contains-anything-from-another-column/td-p/13...

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.

[mahoneypat]

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