Forum Discussion
How to write between function in power bi
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.
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
6 Replies
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.
