I have to create a new column using multiple operators such as && and ||. It seems like it would be an easy update but I can’t seem to get it to work. I am also using a containsstring and that might be messing up the column as well?
There are two compliance Rating Columns. The first is what i created to start and works fine, then i had new requirements added and that column now breaks?
A bonus would be to have this in PowerQ, but I’ll settle for simply fixing my DAX.
test data.csv (14.6 KB)
Compliance Rating New Column.pbix (29.0 KB)
1 Like
Compliance Rating New Column for Chad Sharpe.pbix (29.9 KB)
Pretty sure I got this working as you wanted, you weren’t closing off the CONTAINSSTRING functions individually. You were trying to close them off all at the end.
So, after each of the enclosed text strings, for example “TIVOLI” you needed to close off the expression with a ) and then your double || pipe
WHAT!!! I won’t embarrass myself by saying how many hours i spent messing around with that syntex… lol
I’ll test it out over the weekend…
Thanks!
2 Likes
Ha, don’t worry, we’ve all been there.
Ugh… my logic looks wrong or i am using the && and || incorrect.
Example
Widget C3QAQA has a new rating of (Compliant - Very High). However, it should be (Complaint - Low)
Based on the value I would expect Widget C3QAQA to be captured by the below logic.
My understanding of the below logic… widget has to have “Low” for Availability and Has to have one the 6 other tools in the Tools column.
I don’t understand how the App has a “complaint - Very high”?
Compliance Rating New Column for Chad Sharpe.pbix (30.4 KB)
[Availability] = “Low” &&
CONTAINSSTRING([Tools - Total Tools], “TIVOLI”) ||
CONTAINSSTRING([Tools - Total Tools], “SNSU”) ||
CONTAINSSTRING([Tools - Total Tools], “SCOM”) ||
CONTAINSSTRING([Tools - Total Tools], “DATADOG”) ||
CONTAINSSTRING([Tools - Total Tools], “SPLUNK”) ||
CONTAINSSTRING([Tools - Total Tools], “BYOT”),
“Compliant - Low”,
1 Like
I only focused on the misplacement of the )
Long day for me so didn’t fully check the logic, will have another look.
1 Like
I think you will need to review your logic
In this segment you are referring to the same column [ Tools - Total Tools] twice in the same rule…it cannot be be “SPLUNK” AND “DYNATRACE” at the same time.
It’s the same thing repeated in each section below. What is the logic meant to be?
I noticed that, take a look at the updated PBX I had attached on the last response.
What I notice now is that I don’t know how to logically write the below statement?
In English it should be…
Widget must have Availability equal to “Medium” AND Tools MUST contain “Splunk” AND Tools Must contain “DYNATRACE” && Tools must have either one of the other 4 tools.
if all that is met then it’s “Compliant - Medium”
[Availability] = “Medium” &&
CONTAINSSTRING([Tools - Total Tools], “SPLUNK”) &&
CONTAINSSTRING([Tools - Total Tools], “DYNATRACE”) &&
CONTAINSSTRING([Tools - Total Tools], “TIVOLI”) ||
CONTAINSSTRING([Tools - Total Tools], “SNSU”) ||
CONTAINSSTRING([Tools - Total Tools], “SCOM”) ||
CONTAINSSTRING([Tools - Total Tools], “DATADOG”),
“Compliant - Medium”,
Hello @chad.sharpe
Good to see that you are having progress with your inquiry.
@DavieJoe, Thank you very much for helping out with this inquiry. Chad posted a follow up question and we hope you can still assist. Thank you!
Compliance Rating New Column for Chad Sharpe UPDATED.pbix (30.5 KB)
@chad.sharpe Can you review this please and let me know.
One thing I would add, it’s best to use fully qualified column references when created Calculated Columns, helps avoid confusion or ambiguity.
Column and measure references in DAX - DAX | Microsoft Learn
2 Likes
Hi @chad.sharpe,
Just following up if the response from @DavieJoe helps you solve your inquiry.
If it did, please mark his answer as the SOLUTION.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
The issue was with how i was writing my logic. I broke it down into smaller sections and played with the formatting and finally got it to work.
For Example: I added in multiple OR statements, for some reason using multiple || did not give me the same answer.
[Availability] = “Very High” &&
[Tool - SPLUNK] = “Splunk” &&
[Tool - DYNATRACE] = “Dynatrace” &&
OR([Tool - DataDog] = “DataDog”,
OR([Tool - SCOM] = “SCOM”,
OR([Tool - SNSU] = “SNSU”,
[Tool - TIVOLI] = “Tivoli”))),
“Compliant - Very High”,
1 Like
Glad it is resolved mate, nothing worse than a problem that drags on yet you know you are close to solving.
1 Like
