Forum Discussion
IF with text value & AND OPERATOR
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,
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.
7 Replies
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)
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.
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
