How to write between function in power bi

[Priya_K]

I am new to Power BI. I am trying to create a calculated field using between or if functions.

If the number in Column A is between 17 & 25 I want the result to be displayed as Critical & Improve

I am getting an error if I use between function . When I use if function with and in it, it is giving me an error. 

What am I doing wrong?

Thank you. 

[edhans]

If you are writing it as a calculated column in DAX, you want to use 

=IF(
  Table2[Tickets]>2 && Table2[Tickets]<4
  ,1
  ,0
)

You could use an AND() funciton in there, but AND() only allows two conditions, whereas you can use && until the cows come home. 

If you are doing it in Power Query before it gets to DAX, (which I recommend for performance reasons), add a custom column then use:

if [Tickets] > 2 and [Tickets] < 5 then 1 else 0

Power Query can have multiple and statements. And or statements. And levels with parenthesis. Much more powerful.

[Priya_K]

Thank you.

Yes, I prefer Power Query. I am getting an error when I use and function in there.  

[edhans]

Can you share that error, and maybe a screen shot of it? 

[Priya_K]

It worked when I use "&" instead of "and" in my formula in power query.

IF(
('Work Items - All history by month'[Inherent Risk Score]>17) &&
('Work Items - All history by month'[Inherent Risk Score]<25),
"critical",

[edhans · Accepted solution]

---

@Priya_K wrote:

It worked when I use "&" instead of "and" in my formula in power query.

IF(
('Work Items - All history by month'[Inherent Risk Score]>17) &&
('Work Items - All history by month'[Inherent Risk Score]<25),
"critical",

---

That is DAX. DAX uses a function similar to Excel - IF(condition, true, false) and as I noted above, you should use &&.

Power Query uses this - and it is case sensitive.

if condition then true else false

And if you have multiple conditions, then it is

if condition and condition and condition then true else false

[Priya_K]

Thank you very much.