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

    • 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]))))))))))))))))))))

      • PowerBIBeginer's avatar
        PowerBIBeginer
        Helper V
        7 years ago

        Hi Board, I believe that I can post this query if i found any similar pattern of query..?? Or else, i can post new one..