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

53 Replies

Anonymous
Not applicable
9 years ago
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
wonga
Continued Contributor
9 years ago
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
- evalromf
  Helper I
  9 years ago
  Tanks for the assistance, and sorry for my late feedback ...
  
  I've managed to solve this in a completly different way - I added the ID in a data preparation stage, i.e. before the data is fed into Power BI. This is actually a more neat way to manage the IDs, as I need the data to be properly cleansed for other applications as well (i.e. not just in Power BI).
- rayinOz
  Helper III
  9 years ago
  I'm needing something similar to this... except with adding an OR...
  
  Here's my scenario:
  
  I have a [Course Name] column and a [Organisation / Portfolio] column.
  
  Basically,
  
  If [Course Name] = "Managing Information" or "Promoting Positive Workplace Behaviour"
  
  AND [Organisation / Portfolio] = "University House" or "CAVAL" ....... there is about 11 of them
  
  if true ... "Exclude" and if false "Include"
  
  How would I write this function?
  
  Thanks for any help....
  
  RayinOz
  - GilbertQ
    Super User
    9 years ago
    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
  Helper I
  9 years ago
  Great, this (an some other modifications from my side) helped me out. Thanks!
Anonymous
Not applicable
8 years ago
Hi,

I need to do an IF(AND statement that has more than 2 AND conditions e.g.

= IF(AND(Table[FieldX] = "1",AND(Table[FieldY] = "2",AND(Table[FieldZ] = "1")
,"True",
"False")

Is this possible in PBI or am I only limited to 2 AND conditions? Is there a workaround anybody knows of?
RodrigoTXRA
Helper I
8 years ago
HI All

Not sure this is the right topic for my issue, but I have an excel cell which can have by out of 4 consonants and they are represented as follows:

F = 70
P = 80
G = 100
E = 116

I'm trying to create a DAX new measure (Trying to bring this formula from excel into Power BI) which would look into the field and transform it into a number as per above.

Would anyone know how to do this?

Appreciate any feedback
Regards
Rodrigo
- GilbertQ
  Super User
  8 years ago
  Hi there
  
  When you say create a DAX measure, what is the expected result from your data you posted?
  - RodrigoTXRA
    Helper I
    8 years ago
    HI GilbertQ thanks for your reply.
    
    I basically need the measure to look into a field and if it finds one of those four letters, to transform it into the respective number.
    
    something like this
    Measure = IF("E", then "116", or "P", then "80", or "G" then "100", or "F" then "70"
    
    I just don't know the correct syntax in Power BI to perform the above.
    
    Appreciate your assistance
    Regards
    Rodrigo
RodrigoTXRA
Helper I
8 years ago
Hi All

Just to give you more feedback about my data, this is what I have:

1 - An excel table which contains a column (Request (Project & Ad Hoc) where one of these 4 letters can be found, and each letter represents a number as follows

F = 70
P = 80
G = 100
E = 116

2 - So I require to transform these letters into their respective values so I can use it in further calculations.
- GilbertQ
  Super User
  8 years ago
  Hi there
  
  Will it have the individual number in the column?
  
  If not if you could post some sample data of what the column currently looks like
  - RodrigoTXRA
    Helper I
    8 years ago
    GilbertQ please see screen shot of the excel table below. The column in black is the one I have issues with. This column comes with a letter instead of those numbers. Let me know if you require more info
Anonymous
Not applicable
6 years ago
Hi all,

I have 2 measures (M1, M2) which are working fine in two separate scenarios. But now i need to combine these two scenarios.

I have to put a simple condition in a final measure (M3) using column "Billing"

M3 = If ( Billing = "FP", M1,M2)

But I am unable to use column name in the measure. Please help.
- GilbertQ
  Super User
  6 years ago
  Hi there
  
  Should it not be the following:
  
  M 3 = IF(VALUES(TableName[Billing]) = "FP",[M1],[M2])
  - Anonymous
    Not applicable
    6 years ago
    
    Before I was getting proper OutputNew measure is not working fine
    
    Hi GilbertQ
    
    I tried this but when i am using it in report its not working fine as the report contains the columns from other tables like T1, T2
Anonymous
Not applicable
6 years ago
Hi GilbertQ BeforeOn using new measure

The new measure is not working fine as the report contains columns from some other tables like T1, T2.

Can you suggest on this
- GilbertQ
  Super User
  6 years ago
  Hi there
  
  If you can post some sample data, with the measures we can then assist?
  - Anonymous
    Not applicable
    6 years ago
    Hi GilbertQ
    
    Its difficult to post data as it is coming from two or three tables and they contain many columns. But can you explain why i am not able to put a simple condition.
    
    Example if am i am doing (billing = "FP") it should not give at row level but at the aggregated level without being affected by other columns in the report.
    
    How can i create a measure so that it gives billing = FP without getting affected by any dimension??