IF with text value & AND OPERATOR

[schoden]

Hi All, 

Has anyone faced same problem like mine.  I am making new column with IF statement for text value column with condition of AND OPERATOR. 

Although there exist data in dataset , the IF column returns only false or 0 as I have put it. 

new column :

IF2 = IF( AND(table[column] = "Mobile Accessories", table[column] = "Mobile Devices"), 1, 0)

Here the dataset has same opportunity with category Mobile devices and Accessories.  

The column of IF returns only false value as shown above, I am thoroughly confused what could be the reason ?

Please share your knowledge.

Cheers, 

[amitchandak]

@schoden , Or or In is needed

IF2 = IF( table[column] in { "Mobile Accessories", "Mobile Devices" }, 1, 0)

IF2 = IF( OR(table[column] = "Mobile Accessories", table[column] = "Mobile Devices"), 1, 0)

[schoden]

Hi @amitchandak  Thank you again , the first formula did return both 0 and 1 in the field option but choosing 1  it didnt seem to be fitlering as required.  

The filter didn't return only those oppportunities that has both Mobile device and Accessories, it but returned all opportunities either having just Devices or Accessories.

As shown in picture, only one opportunity has both device and accessory.

[amitchandak]

@schoden , if for the same set id (Device) if both are there

IF2 = if(countx(filter(Table, [device] =earlier([device]) && table[column] in { "Mobile Accessories", "Mobile Devices" }) ,[device])+0>=2, 1, 0)

Now if the can repeat you need to have a distinct count 

IF2 = if( calculate(distinctcount(table[column]) , filter(Table, [device] =earlier([device]) && table[column] in { "Mobile Accessories", "Mobile Devices" }))+0>=2, 1, 0)

Both are new columns 

[schoden]

Hi @amitchandak 

IF2 = if(countx(filter(Table, [device] =earlier([device]) && table[column] in { "Mobile Accessories", "Mobile Devices" }) ,[device])+0>=2, 1, 0)

Would you explain why [device ]+0>=2,  at the end does ? 

[schoden]

@amitchandak 

Hi I used the first formual with opportunity ID which is same for the category of device and Accesssories, It returned the correct list but picked up some opportunity ID with  just devices also still.

[schoden · Accepted solution]

Hi All,

I am posting for others reference as the issue with AND operator is if it is to compare two different columns it works fine but when is to compare two values from same column the AND operator or IN operator doesnt work.

In such case you have to use nested IF.

Cheers.

[schoden]

Hi All , 

I thought I will share the best solution I found for my issue stated above.  It  works for any issue like one customers purchasing Product A and Product B ..Pretty much like What I was after.

After severel internet search, I found an article on Basket Analysis and a video tutorial : 

https://www.youtube.com/watch?v=8PUNpVAiRAE 

Cheers, 

Have a good day.