Forum Discussion
Multiple IF, AND conditions
Struggling with learning some of the basics and have researched online too but finding it hard to unravel. Grateful for any assistance in trying to formulate the below, columns 6 to 9. Thanks in advance..
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |||||
| Date | StoreNumber | Leader | Subsidy | Subsidy outcome | Subsidy Forecast | On Assessment | Assessment Count | Subsidy Sum | expecting (from 6 to 9) | ||||
| 15/04/2025 | 351 | Keith ON | 75 | TRUE | IF subsidy outcome TRUE and IF Date[Column] AFTER (2025,4,6) , true: (2025,4,30) LESS Date[Column] calculation, false: 10 THEN multiply chosen number of days by Subsidy[Column] | IF Leader CONTAINS "ON" and IF Subsidy[Column] between -100 to 100, true: 1, false: 0 | SUM count of ASSESSMENT[Column] using StoreNumber | IF Assessment Count[Column] greater than 1 THEN using StoreNumber SUM Subsidy[Column], otherwise return 0 | 1125 | 1 | 2 | 160 | |
| 16/04/2025 | 774 | Mark OFF | -90 | FALSE | |||||||||
| 17/04/2025 | 83 | Sharon OFF | -95 | FALSE | |||||||||
| 18/04/2025 | 575 | Julie OFF | -70 | FALSE | |||||||||
| 19/04/2025 | 192 | Scott OFF | -60 | FALSE | |||||||||
| 20/04/2025 | 351 | Paula OFF | -55 | FALSE | |||||||||
| 21/04/2025 | 575 | Barry OFF | -75 | FALSE | |||||||||
| 22/04/2025 | 351 | Paula ON | 85 | TRUE | IF subsidy outcome TRUE and IF Date[Column] AFTER (2025,4,6) , true: (2025,4,30) LESS Date[Column] calculation, false: 10 THEN multiply chosen number of days by Subsidy[Column] | IF Leader CONTAINS "ON" and IF Subsidy[Column] between -100 to 100, true: 1, false: 0 | SUM count of ASSESSMENT[Column] using StoreNumber | IF Assessment Count[Column] greater than 1 THEN using StoreNumber SUM Subsidy[Column], otherwise return 0 | 680 | 1 | 2 | 160 |
Hi JK-1,
Thank you for reaching out in Microsoft Community Forum.
The only issue with the formula you posted is a small syntax error in the IF condition. In DAX, all conditions need to be separated by logical operators like &&, and each condition must return a TRUE/FALSE result.
please use the below DAX to resolve the error;
OnFlagCC =
IF (
SubsidyData[StoreNumber] = 83 &&
CONTAINSSTRING(SubsidyData[Leader], "ON") &&
SubsidyData[Subsidy] >= -100 &&
SubsidyData[Subsidy] <= 100,
1,
0
)Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
7 Replies
Hi JK-1 ,
I tried to implement the above scenario and please find the attached pbix file.
Multiple IF, AND conditions.pbixPlease let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
Thank you so much, seeing this presented helped me work through it in stages.
I see I had an initial conflict in that one leader's name contains On and also had an OFF .. ie Sharon OFF but the logic has very much helped in unravelling.
The only other thing I had a different difficulty on was trying to combine queries within IF using other columns. Whilst I have seen && THEN , { } etc I think I'm struggling with the basics on how to adapt where necessary..
Obviously don't want to take up more of your time with something that is probably simple, and not initially discussed in the opener. Since you were very kindly able to help answer everything in the question(s) posed. Cheers,
combine .. use only on StoreNumber given, how to?
OnFlagCC =IF( SubsidyData[StoreNumber]="83"CONTAINSSTRING( SubsidyData[Leader], "ON" )&& SubsidyData[Subsidy] >= -100&& SubsidyData[Subsidy] <= 100,1,0)Hi JK-1,
Thank you for reaching out in Microsoft Community Forum.
The only issue with the formula you posted is a small syntax error in the IF condition. In DAX, all conditions need to be separated by logical operators like &&, and each condition must return a TRUE/FALSE result.
please use the below DAX to resolve the error;
OnFlagCC =
IF (
SubsidyData[StoreNumber] = 83 &&
CONTAINSSTRING(SubsidyData[Leader], "ON") &&
SubsidyData[Subsidy] >= -100 &&
SubsidyData[Subsidy] <= 100,
1,
0
)Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
Hi JK-1,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Thank you,
Pavan.Hi JK-1,
Can you please provide more details about the calculation/formulas or business definition fo the new columns 6 to 9?Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
Hi JK-1,
Thank you for reaching out in Microsoft Community Forum.
Thank you maruthisp , ryan_mayu for the helpful response.
As suggested by maruthisp, I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Please continue using Microsoft community forum.
Regards,
Pavan.
