Best Approach to Handle Multiple Nested OR's & AND's

[Anonymous]

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?

[V-lianl-msft · Accepted solution]

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
     )

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft · Accepted solution]

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
     )

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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?

[V-lianl-msft]

Hi @Anonymous ,

Sorry, I can only share .pbix through sharing link for the time being.

I haven't tested the performance differences you mentioned, but it's easier to maintain data with table.

You can also use the “VAR” function to create variables for SUMX....... to optimize DAX performance.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Can you take screenshot and attach here? I'm trying to understand how you joined the new table with the actual one ? is it in Data Modeling ? Or there is no join required between the actual and new table?

[Anonymous]

HI @V-lianl-msft , can you share the screenshots?

[Anonymous]

I wrote something like this, but i am not getting the expected result. what is missing?

Color Schedule = IF(ISBLANK('Request to Close/Cancel'[Scheduling Comments]),"Yellow" ,IF(CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"cancel"),"#BFBFBF",IF( NOT (CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"close")) || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"closing") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"closure") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"complete") && NOT (CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"do not bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"approval to bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"don't bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"no bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"approved effort hour") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"actuals approved") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"have been bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"hours to bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"approved hour") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"hours approved") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"approved effort hour") || CONTAINSSTRING(('Request to Close/Cancel'[Scheduling Comments]),"total approved contractual") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"bill") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"hours")) && CONTAINSSTRINGEXACT('Request to Close/Cancel'[Billing Method],"Non-Billable - Internal") && CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"billable hour" ) || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"hours billable" )|| CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"hours billable" ) || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"billable amount") || CONTAINSSTRING('Request to Close/Cancel'[Scheduling Comments],"approved hrs" ) ,"Yellow","White")))

[Anonymous]

any inputs?

[V-lianl-msft · Accepted solution]

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.

[Anonymous]

DAX vs TABLE ? Which one will perform good?

[Anonymous]

I can understand this is something lookup concept in ETL. It looks like you used DAX functions which i know by name not by functional, so i have to dive into those functons to understand them.

How about the performance difference between the table concept vs Nested ORs/AND's ?

[amitchandak]

You can use Switch True.

For Or you can use ||

for And you can use &&

Yoy have AND and OR functions too