topic Re: Nested if statements/Switch function? in DAX Commands and Tips

Nested if statements/Switch function?

Lexi — Thu, 30 Dec 2021 15:05:46 GMT

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

Customer 1001 = US Postal Code

Customer 1002 = Europe Postal Code

Customer 1003 = Asia Postal Code.

An issue appears when the customer moves or if they order for another customer. So I want to build in a function or create a custom column (that can be used in a filter). I am using the switch function for the normal customers but I need away to include the abnormal cases.

Re: Nested if statements/Switch function?

ValtteriN — Thu, 30 Dec 2021 17:57:58 GMT

Hi,

Unfortunately, your post is rather difficuilt to understand and it would be good if you provided more information. For now, the only help that I can give is to show some patterns/tips that I use in my models when I want to categorise customers in measures:

First typically I create a SWITCH + TRUE structure where it goes something like this:

SWITCH(TRUE(),
PLACE IF CONDITION HERE, "Category 1",
PLACE IF CONDITION HERE, "Category 2",
PLACE IF CONDITION HERE, "Category 2")

Then I create a table which has these states to use as a filter:

Table = {("Category 1"),("Category 2"),("Category 3")}

Finally I create a filter measure to use in my visuals:
Customer category switch filter:= IF(
or(not(HASONEFILTER('Table'[Value])),[Customer category]=max('Table'[Value])),1,0)

I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: Nested if statements/Switch function?

smpa01 — Thu, 30 Dec 2021 18:20:17 GMT

Lexi need more info, provide sample data and expected output.

Re: Nested if statements/Switch function?

Lexi — Tue, 04 Jan 2022 18:27:14 GMT

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

Re: Nested if statements/Switch function?

smpa01 — Tue, 04 Jan 2022 18:50:42 GMT

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

Re: Nested if statements/Switch function?

smpa01 — Wed, 05 Jan 2022 21:21:36 GMT

Lexi did you try this out yet?

Re: Nested if statements/Switch function?

Lexi — Fri, 07 Jan 2022 20:24:12 GMT

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! 🤞

Re: Nested if statements/Switch function?

Lexi — Mon, 10 Jan 2022 18:16:13 GMT

Hi all,

Thanks for all your contribution.

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

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

Re: Nested if statements/Switch function?

smpa01 — Mon, 10 Jan 2022 18:18:53 GMT

Lexi can you please create a new thread with sample data, expected output?