Nested IF formulas with OR statement

[Elder22]

Hello,

I have tried to research this issue on a few posts but cannot apply the solutions to solve my problem.

I am trying to measure opportunities against two seperate sets of criteria, I want to count the total opportunity value if either of them are true.

I am trying to do this by creating a custom column, but get syntax issues early on with how I am building this.

In plain english, the statement is:

IF (

The close date is greater than 1st Jan,

The stage name is not closed lost,

and the team member is not blank,

the datediff support sub is 0

and the sales support submission date is >01/01/2017)

OR

(The close date is between 1st Jan and 31st Dec 2017,

The stage name is not closed lost,

and the team member is not blank,

Date diff close date is 0

And the Forecast category = “Commit”)

THEN

Total Orders

So basically only count the total orders upon satisfying either of the two nested criteria. Can anyone help me build this in a way Power BI can work with?

Thanks in advance.

Ian

[kcantor · Accepted solution]

@Elder22

Break it down into two if statements using 1 for the positive result and 0 for the negative result. Then write a third if statement referencing the two if statements to week out the negatives. Finally, use that last statement in a Calculate.

 Sample = CALCULATE([Total Orders], [Both Criteria]=1)

Of course if you provide sample data or a snip we can better help you solve this. I am just spitballing ideas based solely upon your question.

[Elder22]

Hi all,

Apologies about the delay on this - after several iterations based on some great help from the minds here I managed to solve the problem.

I used three new columns (perhaps not the most efficient) -

Commit bucket = IF(AND(Opportunity[DateDiff Close date]=0,Opportunity[ForecastCategory]="Forecast"),Opportunity[Total orders],0)

Closed Won bucket = IF(AND(Opportunity[DateDiff Support sub]=0,Opportunity[Stage Name]="Closed Won"),Opportunity[Total orders],0)

To add together to make this:

Forecast (Commit number) = Opportunity[Closed Won bucket] + Opportunity[Commit bucket]

This allows me to run two seperate counts in the first columns, and then add together a total with the final one.

Thanks for all the help

[v-caliao-msft · Accepted solution]

@Elder22,

Could you please provide us more detail information about your data model? Generally, we can add multiple conditions in IF function, such as

Measure  =  IF((Condition1 && Condition2 && Condition3) || (Condition4 && Condition5), "Ture value","False Value")

Regards,

Charlie Liao

[kcantor · Accepted solution]

@Elder22

Break it down into two if statements using 1 for the positive result and 0 for the negative result. Then write a third if statement referencing the two if statements to week out the negatives. Finally, use that last statement in a Calculate.

 Sample = CALCULATE([Total Orders], [Both Criteria]=1)

Of course if you provide sample data or a snip we can better help you solve this. I am just spitballing ideas based solely upon your question.

[bullius]

Hi @Elder22,

Could you show the formula you have used with the error message?