If Statement or Conditional Column - Which and Why

[irnm8dn]

In my data set I have a customer 5 digit customer ID in two formats (Text and Whole Number). 

I wrote a DAX statement which is accepted but not working correctly:

BuyerProspect= IF(Sheet2[Customer ID]="12345"||"67890"||"12121"||"23232"||"45454"||"67676"||"89898"||"99999"||"Buyer","Prospect")

It does not provide me the correct variables and shows all rows as "Prospect". 

I am basically trying to say...if the Customer ID mateches any of these values, then "Buyer", else "Prospect"

Alternatively, if I use a Conditional Column in Query Editor - I get the desired results.

I am using the text format to drive the result. 

So here's my questions:

1.  Which is the preferred format for getting a column with correct label?

2.  Why isn't the DAX statement working properly?

3.  Why does the conditional column work, and it is correct to use?

Thanks!

[ChrisMendoza]

@irnm8dn -

---

@irnm8dn wrote:

So here's my questions:

1.  Which is the preferred format for getting a column with correct label?

2.  Why isn't the DAX statement working properly?

3.  Why does the conditional column work, and it is correct to use?

Thanks!

---

1. I think it's personal preference, I'm leaning towards IF ( ) with your data as it's less typing.

BuyerProspect =
IF (
    Table1[Customer ID] = "12345"
        || Table1[Customer ID] = "67890"
        || Table1[Customer ID] = "12121"
        || Table1[Customer ID] = "23232"
        || Table1[Customer ID] = "45454"
        || Table1[Customer ID] = "67676"
        || Table1[Customer ID] = "89898"
        || Table1[Customer ID] = "99999",
    "Buyer",
    "Prospect"
)

versus; where' you have to type "BUYER" each time.

Using SWTICH( ) = 
SWITCH(
    TRUE(),
    Table1[Customer ID] = "12345", "BUYER",
    Table1[Customer ID] = "67890", "BUYER",
    -- etc, etc
    "Prospect"
)

I'm unsure of the performance issues. Maybe someone else knows.

2. One reason is mentioned by @Aron_Moore , but also you didn't include [Customer ID] in the statement

3. Unsure of what you mean on this. It was not working correctly. If the [Customer ID] is already in your table then are they not already a "Buyer"? I suppose I would need more information to determine if either of these are the best solutions.  Oh I see, in the Query Editor. Probably because the software is helping you put the information in the correct spot so you can't mess up the formula/function. Again, I think it is personal preference. I would likely do in the Query Editor myself.

[Aron_Moore]

Looks like a typo in your DAX:   "99999"||"Buyer","Prospect"

should probably be  "99999" , "Buyer","Prospect"