Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
)
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.
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?
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.
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?
I wrote something like this, but i am not getting the expected result. what is missing?
any inputs?
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.
DAX vs TABLE ? Which one will perform good?
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 ?
You can use Switch True.
For Or you can use ||
for And you can use &&
Yoy have AND and OR functions too
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |