Multiple IF Conditions in Custom Column

[prakash11440278]

Hi All,

I am calculating Age Category DAX formula based on the Incident logged. But i want below DAX formula to be replicated in Custom Column in Query Editor for Sorting purpose. Please provide the Custom Column formula for the below calculated column.

Age Bucket = IF('INC Logged'[Age of Incidents]>=0 && 'INC Logged'[Age of Incidents]<30,"0-30",
IF('INC Logged'[Age of Incidents]>=30 && 'INC Logged'[Age of Incidents]<60,"30-60",
IF('INC Logged'[Age of Incidents]>=60 && 'INC Logged'[Age of Incidents]<90,"60-90",
IF('INC Logged'[Age of Incidents]>=90 && 'INC Logged'[Age of Incidents]<180,"90-180",
"180+")))
)

Thanks In Advance.

[Anonymous]

IF the value in Col A and Col B is equal to 0 then it should give me 1 else 0 along with including this third condition as if A and B both are equal to 0 it should give me "NA". This is not working for me while adding a formula in new column in power BI. How do i resolve it?

This is the condition as per Excel formula: =IF(AND(E5=0,F5<>0),1,IF(AND(E5=0,F5=0),"na",0)). However; this is not working in Power BI.

[PrasanthKumar · Accepted solution]

Hi,

Can you please try this custom column query: 

(if ([Age of Incidents]>=0 and [Age of Incidents]<30) then "0-30" else  if ([Age of Incidents]>=30 and [Age of Incidents]<60) then "30-60" else if ([Age of Incidents]>=60 and [Age of Incidents]<90) then "60-90" else if ([Age of Incidents]>=90 and [Age of Incidents]<180) then "90-180" else if ([Age of Incidents]>=180 and [Age of Incidents]<90) then "180+" else "")

Thanks,

Prasanth.C

+91 9043194307

[Anonymous]

Hi All,

Thanks @PrasanthKumar for this.

I've done something similar but now I need to build on it, replacing the values with parameters, so the client can edit the thresholds for each band. So based on the example below, my script looks like this:

CustomColumn = 

(if ([Age of Incidents]>=#"Parameter1" and [Age of Incidents]<#"Parameter2") then "0-30" else  if ([Age of Incidents]>=#"Parameter3" and [Age of Incidents]<#"Parameter4") then "30-60" else "")

... and so on. Error reads:  The following syntax error occurred during parsing: Invalid token line 2, offset 26, #.

Any help would be gratefully received.

Thanks,

Kevin

[prakash11440278]

Hi PrasanthKumar,

Its Working. Thanks for your help....

[PrasanthKumar]

Cool..always welcome..!!! Have a good day..!!

[PrasanthKumar]

Hi,

Can you please try this custom column query :

(if ([Age of Incidents]>=0 and [Age of Incidents]<30) then "0-30" else  if ([Age of Incidents]>=30 and [Age of Incidents]<60) then "30-60" else if ([Age of Incidents]>=60 and [Age of Incidents]<90) then "60-90" else if ([Age of Incidents]>=90 and [Age of Incidents]<180) then "90-180" else if ([Age of Incidents]>=180 and [Age of Incidents]<90) then "180+" else "")

Please let me know whether you got solution or not.

Thanks,

Prasanth.C

+91 9043194037

[Anonymous]

If you are looking for sorting only then You can do the sorting in the visuals/ data view also.

Thanks
Raj

[prakash11440278]

Hi Rajendran, Thanks for your reply.
I believe we cannot sort the data based on the calculated column created in PBI Desktop and the format of the data type is not interger. We can sort the data based on numerical values, that can be done by adding new conditional column in query editor. I tried to create it but multiple conditions not possible using conditional column. If possible can you please provide the custom column formula for the provide calculated column.