Hi, I'm trying to create a new column that should be populated with a certain number based on the contents of several other columns. What I'm looking for is something like this: If [COUNTRY] equals Kazakhstan AND IF [COMPANY] equals Alltel, then "1"Else If [COUNTRY] equals Kazakhstan AND IF [COMPANY] equals VIP, then "2" But i cannot find such a possibility in "Add conditional column". (see picture) And when I try to use the Advanced Editor, I cannot find any syntax that covers this scenario. Does anyone have a solution for this?

You can just use the regular Add Custom Column button and write out a function for the column. Your example pseudocode is missing the final else, by the way, so I made something up. if [COUNTRY] = "Kazakhstan" and [COMPANY] = "Alltel" then 1 else if [COUNTRY] = "Kazakhstan" and [COMPANY] = "VIP" then 2 else 0

Hi there, the following will work, when in the Query Editor, click on Add Column in the ribbon and then Custom Column if [Course Name] = "Managing Information" or "Promoting Positive Workplace Behaviour" and [Organisation / Portfolio] = "University House" or "CAVAL" then "YES" else "No"NOTE: The syntax for the "if" "or" "and" "then" "else" is all case sensitive.

evalromf, Anonymous's suggestion is through Power Query or M. If you prefer DAX, you can add a calculated column by right clicking on the table you want to add the column to and click "New Column". The DAX formula would be something like: MeasureName = IF(AND([COUNTRY] = "Kazakhstan", [COMPANY] = "Alltel"), 1, IF(AND([COUNTRY] = "Kazakhstan", [COMPANY] = "VIP"), 2, "N/A") ) //Might need another else value, not sure

You need to add some paranthesis as and has higher priority than or.In your case the formula should be built like (a=x or a=y) and (b = m or b = n) etcetera.Without paranthesis, a=x or a=y and n=m or b=n is equivalent with a=x or (a=y and b=m) or b=n.So without parenthesis, you can think of all (groups of) ands evaluated first and the ors being in between (groups of) ands.

Hi Anonymous You can create a new Custom Column in the Power Query Editor with the following syntax if [col1] = "a1" and [col2] = "a2" then 50 else if [col1] = "b1" and [col2] = "b2" then 100 else if [col1] = "c1" and [col2] = "c2" then 200 else 999

AND IF in conditional column | Microsoft Fabric Community

Forum Discussion

53 Replies

Related content