Forum Discussion

Lexi's avatar
Lexi
Frequent Visitor
4 years ago
Solved

Nested if statements/Switch function?

Howdy!    I need some direction from you nice guys 😊   I am new to Powerbi and I am trying to get to a solution to my issue. I have a dataset and I am assigning groups to our customers  Custome...
  • smpa01's avatar
    smpa01
    4 years ago

    Lexi  can you try this

    Column =
VAR _home =
    SELECTCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CustID] ) ),
            'Table'[CustID],
            'Table'[Home]
        ),
        "cust", [CustID],
        "country", [Home]
    )
VAR _shipping =
    SELECTCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CustID] ) ),
            'Table'[CustID],
            'Table'[Shipping]
        ),
        "cust", [CustID],
        "country", [Shipping]
    )
VAR _intersect =
    INTERSECT ( _home, _shipping )
VAR _except =
    EXCEPT ( _shipping, _home )
RETURN
    IF (
        MAXX ( _except, [country] ) = BLANK (),
        MAXX ( _intersect, [country] ),
        MAXX ( _except, [country] )
    )

     

    which generates this

     

Lexi's avatar
Lexi
Frequent Visitor
4 years ago

Thanks folks!

I am trying to get at column D so I can use it later on as a filter in the main dashboard.

So I created this formal so far -

POSTAL CODE = Switch([CustomerID],
"Customer1001", "USA",
"Customer 1002", "EU", "Other"

This works for the customers that have no difference. I need to complete the column of the customers that dont conform. I want to try to complete a column with those easy customers that follow correctly and then the ones that dont, but I am unsure how to add the exceptions.

Ie: Customer 1001 if shipping address is europe then the postal code is eu otherwise usa

 

 

smpa01's avatar
smpa01
Community Champion
4 years ago

Lexi  can you try this

Column =
VAR _home =
    SELECTCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CustID] ) ),
            'Table'[CustID],
            'Table'[Home]
        ),
        "cust", [CustID],
        "country", [Home]
    )
VAR _shipping =
    SELECTCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CustID] ) ),
            'Table'[CustID],
            'Table'[Shipping]
        ),
        "cust", [CustID],
        "country", [Shipping]
    )
VAR _intersect =
    INTERSECT ( _home, _shipping )
VAR _except =
    EXCEPT ( _shipping, _home )
RETURN
    IF (
        MAXX ( _except, [country] ) = BLANK (),
        MAXX ( _intersect, [country] ),
        MAXX ( _except, [country] )
    )

 

which generates this

 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Lexi  did you try this out yet?

    • Lexi's avatar
      Lexi
      Frequent Visitor
      4 years ago

      Hi Smpa01, thanks you sir for helping me out!!! 

      I tried out your formual but it aint working for me as I figured out my conditions are more complicated than I first intended. 😢

       

      I have one set of customers (150 in total) following one swich formual correct (VAR a), then about 5 customers that are the outliers and do not follow suit. I meantion two hard ones below (Var B, Var C)

       

      Var a = switch ([customer],
                     "Customer 1001" , "USA",
                      "Customer 1002", "Asia", "Other  ...... and so on
      Var b = if [Customer] = "2002" && [cat] <> "Household" THEN " USA" else "Europe"
      Var c = if [Customer] = "2002" && [order date] > "2021, 10, 1" then "USA" else "Asia"

       

       

      How the do I even try get this into a new custom column? Any direction will help! 🤞