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
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. 

     

    • rayinOz's avatar
      rayinOz
      Helper III
      9 years ago

      Thanks MARCEL! I'll give it a shot in a moment!

      • rayinOz's avatar
        rayinOz
        Helper III
        9 years ago

        So, this is what I have and I get an "error" in the new custom column...

         

        if ([Course Name] = "Managing Information" or "Promoting Positive Workplace Behaviour") 
        and ([#"Organisation / Portfolio"] = "Bio21 Australia Ltd" or "Bio21 Australia Limited" 
        or "CAVAL" or "Grattan Institute" or "Kendall Hall" or "Melbourne University Publishing Ltd" 
        or "Nossal Institute Ltd" or "University House" or "Australian National Academy of Music Ltd" 
        or "Melbourne Theatre Company" or "MU Student Union Ltd") then "Exclude" else "Include"

         

        This the error:

         

  • JoelR's avatar
    JoelR
    New Member
    7 years ago

    Thanks. This was exactly what I needed and super helpful. Do you know of any sites where the general language for Power BI Power Query Editor is described? 

  • PowerBIBeginer's avatar
    PowerBIBeginer
    Helper V
    7 years ago

    Hi GilbertQ,

     

    I have similar query. I'm stucking in below dax formula, and getting an error - "Expressions that yield variant data-type cannot be used to define calculated columns."

     

    Pls suggest me..

     

    Column 2 = IF('For PwerBi'[type for TAT]="MR" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))>8,">8Hrs",

    IF('For PwerBi'[type for TAT]="MR" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))<=8,VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays])),

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("00:30:00"),"<30Min",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("00:30:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("01:00:00"),"30 Min - 1 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("01:00:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("01:30:00"),"1 Hr - 1.5 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("01:30:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("02:00:00"),"1.5 Hr - 2 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("02:00:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("02:30:00"),"2 Hr - 2.5 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("02:30:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("03:00:00"),"2.5 Hr - 3 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("03:00:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("03:30:00"),"3 Hr - 3.5 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("03:30:00") && 'For PwerBi'[TAT excluding weekends_holidays]<TIMEVALUE("04:00:00"),"3.5 Hr - 4 Hr",

    IF('For PwerBi'[type for TAT]="MRR" && 'For PwerBi'[TAT excluding weekends_holidays]>=TIMEVALUE("04:00:00"),"> 4 Hr",

    IF('For PwerBi'[type for TAT]="RNW" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))<8,"< 8 Hrs",

    IF('For PwerBi'[type for TAT]="RNW" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))>=8 && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))<16,"8 - 16 hrs",

    IF('For PwerBi'[type for TAT]="RNW" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))>=16 && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))<24,"16 - 24 hrs",

    IF('For PwerBi'[type for TAT]="RNW" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))>=24 && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))<36,"24 hrs - 36 hrs",

    IF('For PwerBi'[type for TAT]="RNW" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))>=36 && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays])),"36 hrs - 48 hrs",

    IF('For PwerBi'[type for TAT]="RNW" && VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))>=48,"> 48 hrs",

    IF('For PwerBi'[type for TAT]="NA","NA",VALUE(INT(24*'For PwerBi'[TAT excluding weekends_holidays]))))))))))))))))))))