Forum Discussion
Nested if statements/Switch function?
- 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
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!
- Lexi4 years agoFrequent Visitor
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- smpa014 years agoCommunity Champion
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