Multiple IF, AND conditions

[JK-1]

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

[Anonymous]

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.

[Anonymous]

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.

[maruthisp]

Hi @JK-1 ,

I tried to implement the above scenario and please find the attached pbix file.
Multiple IF, AND conditions.pbix

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 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[JK-1]

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

)

[Anonymous · Accepted solution]

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.

[maruthisp]

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 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ryan_mayu]

@JK-1 

not clear about your explaination. could you pls also provide the expected output or provide the formula in excel for reference.