Forum Discussion
Best Approach to Handle Multiple Nested OR's & AND's
All,
I have started to work on a rquirement where the conditional background color needs to be applied for a column (Column Name = Scheduling Comments) based on the below condition.
If Scheduling Comments DOES NOT CONTAIN ("close" OR "closing" OR "closure" OR "complete") AND Scheduling Comments DOES NOT CONTAIN ("do not bill" OR "approval to bill" OR "don't bill" OR "no bill" OR "approved effort hour" OR "actuals approved" OR "have been bill" OR "hours to bill" OR "approved hour" OR "hours approved" OR "total approved contractual" OR "bill" OR "hours") AND Billing Method = "Non-Billable - Internal" AND Scheduling Comments CONTAINS ("billable hour" OR "billable hour" OR "hours billable" OR "billable amount" OR "approved hrs")
I am new to PowerBI and learnt that i have to use Nested OR's & AND's for this in DAX. is that the feasible solution to resolve this? or what is the best way to handle this conditional formatting logic?
Hi Anonymous ,
First, the keywords are split into two columns as a table, one is the keywords to be included and the other is the keywords not included.
Create a measure like this and apply it in conditional formatting:
Measure = IF( NOT(SUMX('Table (2)', FIND( UPPER('Table (2)'[not contains]), UPPER(MAX('Table'[Scheduling Comments])), ,0 ) ))&&MAX('Table'[Billing Method])="Non-Billable - Internal"&& SUMX('Table (2)', FIND( UPPER('Table (2)'[contains]), UPPER(MAX('Table'[Scheduling Comments])), ,0 )), 1, 0 )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
Sorry for the late reply.
First, the keywords are split into two columns as a table, one is the keywords to be included and the other is the keywords not included.And you don't need to create a relationship for two tables.
Then create measure like this:
Measure = IF( NOT(SUMX('Table (2)', FIND( UPPER('Table (2)'[not contains]), UPPER(MAX('Table'[Scheduling Comments])), ,0 ) ))&&MAX('Table'[Billing Method])="Non-Billable - Internal"&& SUMX('Table (2)', FIND( UPPER('Table (2)'[contains]), UPPER(MAX('Table'[Scheduling Comments])), ,0 )), 1, 0 )Apply measure to conditional formatting
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
- amitchandak
Super User
You can use Switch True.
For Or you can use ||
for And you can use &&
Yoy have AND and OR functions too
V-lianl-msftCommunity Support
Hi Anonymous ,
First, the keywords are split into two columns as a table, one is the keywords to be included and the other is the keywords not included.
Create a measure like this and apply it in conditional formatting:
Measure = IF( NOT(SUMX('Table (2)', FIND( UPPER('Table (2)'[not contains]), UPPER(MAX('Table'[Scheduling Comments])), ,0 ) ))&&MAX('Table'[Billing Method])="Non-Billable - Internal"&& SUMX('Table (2)', FIND( UPPER('Table (2)'[contains]), UPPER(MAX('Table'[Scheduling Comments])), ,0 )), 1, 0 )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi V-lianl-msft ,
I am unable to download the sample file which you attached ( your sharepoint link is restricted in our client network).
Can you attach the file in the forum itself?
