Forum Discussion
Custom Column - Nested IF / AND Formula
Hello,
I am working on a project where I need to provide a numerical score from 1-5 based on two criteria: (1) facility type (cateogried as Text) and (2) Cycle time (measured in days and categorized as Whole Number). I am new to PowerBi and still trying to learn the different syntax compared to Excel, but this is how i would write the formula using Excel:
=IF(AND(FacilityType="Hospital",CycleTime<10),5
IF(AND(FacilityType="College",CycleTime<15,5
IF(AND(FacilityType="Other",CycleTime<20,5
IF(AND(FacilityType="Hospital",CycleTime<15),4
IF(AND(FacilityType="College",CycleTime<20,4
IF(AND(FacilityType="Other",CycleTime<25,4
....... So on and so forth.
There are 5 different facility types and 6 different scores i want to provide. However I cant seem to make this work in Power Query as a new Cusotm Column. Does anyone have any suggestions on how to do this?
Thank you!
Joe
Hi Jgrohows ,
It appears you are trying to use the DAX code language in Power Query, although Power Query uses a language called "M".
In your example, the code would look something similar to this:
if [FacilityType] ="Hospital" and [CycleTime] < 10 then 5
else if [FacilityType] ="College" and [CycleTime] < 15 then 5
else if ...
Note, M is case sensitive: "and" and "else" and "if" need to be written in small letters.
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
2 Replies
Hi Jgrohows ,
It appears you are trying to use the DAX code language in Power Query, although Power Query uses a language called "M".
In your example, the code would look something similar to this:
if [FacilityType] ="Hospital" and [CycleTime] < 10 then 5
else if [FacilityType] ="College" and [CycleTime] < 15 then 5
else if ...
Note, M is case sensitive: "and" and "else" and "if" need to be written in small letters.
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thank you very much! I appreciate your quick and very helpful response.
