Multiple IF ERROR

[Anonymous]

Dear,

I wrote the multiple IF for my segmentation, for detail:

Count the order number of each customer id: 

if if customer has total number of orders from 1-5 : classifify them in group 1-5 

if if customer has total number of orders from 6-10 : classifify them in group 6-10

if if customer has total number of orders from 11+ : classifify them in group 11+

Please help me to check my formula is right or not, cause when I filter, it showed me (Blank) and the result of customer from 1-5 is not correct also, please help me to check the formular. 

Thank you and hope to hear from you soon!

[Anonymous]

Hi,

I think you should be using a Summarize or a SummarizeColumns Function.

Expression Step 1: SUMMARIZECOLUMNS(CUSTOMER_ID,"Count",COUNT(CUSTOMER_ID))

This will return a Summarized View with a Group on Customer_ID Column.

Expressions Step 2:ADDCOLUMN(SUMMARIZECOLUMNS(CUSTOMER_ID,"Count",COUNT(CUSTOMER_ID)),"Category",

SWITCH(TRUE(),[Count]>1 && [Count]<5,"1-5",

                          [Count]>6 && [Count]<10,"6-10",

                          "11+")

Do Accept as Solution if it solved your issue 🙂

[Anonymous]

hi, while trying to do that, 

i got the error 

https://we.tl/t-Yvh6el6Jes

Sample link here

Hope you could help me to fix!!

[Anonymous]

Hi,

The Issue is because you are trying to Create a Column on a Place that Creates a Measure. Please Navigate to the Tables View (Data Modeler) (Second Icon from Top on the Left Pane) . and then use this formula.

[Anonymous]

Hi, 

Thank you for your reply

I still got the same issue. 

[Anonymous]

I am sorry. I think you will need to create this as a new table (thru modelling) and then use lookupvalue function to bring values into current table

[AlB]

Hi @Anonymous 

How about using a SWITCH statement? It would probably be simpler in this case.

In any case, do show the code with text directly rather than as part of a screen capture. It's barely visible.

Sample data would also be of help

Cheers

[Anonymous]

Dear, 

Thank you for your quick reply. 

Number Order-UserID = CALCULATE(DISTINCTCOUNT(Sheet1[ORDER CODE]);ALLEXCEPT(Sheet1;Sheet1[USER ID]))

Customer Segmentation = IF(AND(1<[Number Order-UserID];[Number Order-UserID]<6);"1-5";IF(AND([Number Order-UserID]>5;[Number Order-UserID]<11);"6-10";IF([Number Order-UserID]>10;"10+")))

Here is the formula. 

Could you help me to change it into SWITCH, pleaes? I haven't used SWITCH before

Thanks!!