cancel
Showing results for
Did you mean:
Regular Visitor

## IF Formula

Hi Team,

Can some one help me with correcting the below formula?

By Value3 = IF(SUM(Sheet1[Total Value])< 100000,"1. < 100k",IF(SUM(Sheet1[Total Value])>= 1000000,"5. = or > 1 Million",IF(AND(SUM(Sheet1[Total Value])>= 100000,SUM(Sheet1[Total Value])< 250000,"2. 100k - < 250k"),IF(AND(SUM(Sheet1[Total Value])>= 250000,SUM(Sheet1[Total Value])< 500000,"3. 250k - < 500k"),IF(AND(SUM(Sheet1[Total Value])>= 500000,SUM(Sheet1[Total Value])< 1000000,"4. 500k - < 1 Million",""))))))

When i enter this formula encountering the below error.

"too many arguments were passed to the and function. the maximum argument count for the function is 2"

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: IF Formula

Measures needs the row context to show all values.  You could create a calculated column to implement.

```Column =
IF(
Sheet1[Total Value]< 100000,"1. < 100k",
IF(Sheet1[Total Value]< 250000,"2. 100k - < 250k",
IF(Sheet1[Total Value]< 500000,"3. 250k - < 500k",
IF(Sheet1[Total Value]< 1000000,"4. 500k - < 1 Million","5. = or > 1 Million"))))```

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Member

## Re: IF Formula

The AND function can only take 2 arguments.

If you require more than 2 arguments to be satisified you can use && between arguments instead of the AND function.

Hope this helps

Thanks,

George

Regular Visitor

## Re: IF Formula

Thanks For the response.

I have changed the formula. The formula works but i am not getting the result right. Could you help me correcting it.

By Value3 = IF(SUM(Sheet1[Total Value])< 100000,"1. < 100k",IF(SUM(Sheet1[Total Value])>= 1000000,"5. = or > 1 Million",IF(SUM(Sheet1[Total Value])>= 100000 && SUM(Sheet1[Total Value])< 250000,"2. 100k - < 250k",IF(SUM(Sheet1[Total Value])>= 250000 && SUM(Sheet1[Total Value])< 500000,"3. 250k - < 500k",IF(SUM(Sheet1[Total Value])>= 500000 && SUM(Sheet1[Total Value])< 1000000,"4. 500k - < 1 Million","")))))

Its producing only the condition "5. = or >1Million"

Regards,
Ranjan
Member

## Re: IF Formula

That result would be displayed because the value is greater than or equal to 1m.

Are you placing the measure as a card visual or splitting it out in a table visual?

Thanks,

George

Regular Visitor

## Re: IF Formula

I have added it as new column.

If you see the below difference between BI new column formula and excel formula.

Community Support Team

## Re: IF Formula

Measures needs the row context to show all values.  You could create a calculated column to implement.

```Column =
IF(
Sheet1[Total Value]< 100000,"1. < 100k",
IF(Sheet1[Total Value]< 250000,"2. 100k - < 250k",
IF(Sheet1[Total Value]< 500000,"3. 250k - < 500k",
IF(Sheet1[Total Value]< 1000000,"4. 500k - < 1 Million","5. = or > 1 Million"))))```

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 66 members 1,197 guests
Recent signins: