cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

Hi @karthi_powerbi ,

 

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
     )

test_conditional-formatting.PNG

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.

View solution in original post

Highlighted
Microsoft
Microsoft

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

Hi @karthi_powerbi ,

 

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.

test_Best Approach to Handle Multiple Nested OR's & AND's3.PNG

test_Best Approach to Handle Multiple Nested OR's & AND's2.PNG

test_Best Approach to Handle Multiple Nested OR's & AND's1.PNG

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

test_Best Approach to Handle Multiple Nested OR's & AND's4.PNG

 

 

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

View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

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

You can use Switch True.

For Or you can use ||

for And you can use &&

 

Yoy have AND and OR functions too

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Microsoft
Microsoft

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

Hi @karthi_powerbi ,

 

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
     )

test_conditional-formatting.PNG

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.

View solution in original post

Highlighted
Helper III
Helper III

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

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?

Highlighted
Helper III
Helper III

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

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 ?

Highlighted
Microsoft
Microsoft

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

Hi @karthi_powerbi ,

 

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.

Highlighted
Helper III
Helper III

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

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?

Highlighted
Helper III
Helper III

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

DAX vs TABLE ? Which one will perform good?

Highlighted
Helper III
Helper III

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

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

Highlighted
Helper III
Helper III

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

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")))

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors