AND IF in conditional column

[evalromf]

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?

[RodrigoTXRA]

@GilbertQ, YES, this solved the issue. I expect to ge to the calculations and hope everything goes well.

Thank you VERY much for your assistance on this

Regards

Rodrigo

[GilbertQ]

Hi @RodrigoTXRA

Happy to assist and get it working for you.

[RodrigoTXRA]

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]

Hi there

When you say create a DAX measure, what is the expected result from your data you posted?

[RodrigoTXRA]

Hello again

It think that it would be something like this formula below, but the other way around, instead of transforming numbers into text, for the month, it would transform text into numbers.

=SWITCH([Month], 1, "January", 2, "February", 3, "March", 4, "April" , 5, "May", 6, "June", 7, "July", 8, "August" , 9, "September", 10, "October", 11, "November", 12, "December" , "Unknown month number" )

Any ideas?

Regards

Rodrigo

[RodrigoTXRA]

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

[GilbertQ]

Hi there

If you need this to be a column I would suggest making the changes in the Power Query Editor, where it is a lot easier to make the changes you require.
The reason being is that you can use functions like "contains" or looking for specific things.

[Anonymous]

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?

[wonga]

@evalromf,

@KHorseman'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]

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

[rayinOz]

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]

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]

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]

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

[GilbertQ]

Hi there

I would suggest instead of using a calculated column in the data model, do this in the Power Query Editor where you can use the Custom Column, where you can use multiple conditions.

here is my blog post explaining this: https://www.fourmoo.com/2018/11/27/multiple-conditions-for-a-conditional-column-in-power-query/

[PowerBIBeginer]

Hi,

On your solution, it is not written how to get custom column. I got it now, its difficult me to find that. Okk..

As now im trying in custom column, how do i mentioned this line-

IF[Cond1]="abc" and INT(24*[TAT excluding weekends_holidays])>8, then ">8Hrs"

else if ...

INT(24*[TAT excluding weekends_holidays])>8 ??

Can i use INT above? Or directly like

IF[cond1]="abc" and 24*[TAT excluding weekends_holidays])>8 then ">8Hrs"

else if ...

[PowerBIBeginer]

Hi, I do some random check.

When I'm using single if condition like-

Column = IF('For PwerBi'[Cert type for TAT]="Midterm Routine" && INT(24*'For PwerBi'[TAT excluding weekends_holidays])>8,">8Hrs","NA")

im not getting an error and output comes.

But, When im adding one IF condition like-

Column = IF('For PwerBi'[Cert type for TAT]="Midterm Routine" && INT(24*'For PwerBi'[TAT excluding weekends_holidays])>8,">8Hrs",IF('For PwerBi'[Cert type for TAT]="Midterm Routine" && INT(24*'For PwerBi'[TAT excluding weekends_holidays])<8,INT(24*[TAT excluding weekends_holidays]),"NA"))

it giving me an error - "Expressions that yield variant data-type cannot be used to define calculated columns."

I dont understand meaning of this error. Why it appears. Someone pls help me in this.

[PowerBIBeginer]

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

[GilbertQ]

Hi there

Glad we got this part working.

Could I suggest for your other issue to create a new post?

Easier to keep them separate.

[PowerBIBeginer]

Sorry for again. Can you also guide me how do i get Power Bi Pro free login option..

[JoelR]

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?