Forum Discussion

evalromf's avatar
evalromf
Helper I
9 years ago

AND IF in conditional column

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 [COUNT...
Need Help
wonga's avatar
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's avatar
    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's avatar
    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's avatar
      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.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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. 

         

  • evalromf's avatar
    evalromf
    Helper I
    9 years ago

    Great, this (an some other modifications from my side) helped me out. Thanks!