Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Priya_K
Regular Visitor

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. 

 

1 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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you.

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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",


@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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you very much. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.