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

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

      • PowerBIBeginer's avatar
        PowerBIBeginer
        Helper V
        7 years ago

        Hi GilbertQ, Thank You so much for sharing an answer. As well as Thanks for showing me this new website, this too help me to learn a lot.

        About solution you have provided, i think that option is available in Power BI Pro option, wherein im using desktop version.

        I will try with pro version. Thanks again..