Forum Discussion
Nested IF formulas with OR statement
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
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.
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
4 Replies
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.
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
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
