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.
Hi all,
Completely stuck and hoping there is a quick answer. I need to return a three possible numerical values depending on the result of a search in another field. It's probably as simple to show you the formula I used in Excel that works perfectly:
=IF(OR(P2="RESOLVED",P2="CLOSED"),IF(ISNUMBER(SEARCH("Find this Text!",AR2)),10,-10),"")
It's for a case logging system and when the issue is initially resolved or eventually closed, a template should have been used. This scores the case based on whether that happened or not. It leaves the score blank if the case is still open.
Here's my version for a column in Power BI. Initial adjustments made to satisfy that I wanted the field to be numerical, so it should assign a "0" if the case is open. And I had to specify a 'value if not found':
Template = if(or(Data[State]="RESOLVED",SostenutoData[State]="CLOSED"),if(isnumber(search("Find This Text!",Data[Resolution Detail],,0)),10,-10),0)
It doesn't return an error, but it doesn't work either! No matter what I try it returns "10" for everything. It does catch 'open' cases and gives them "0", but I can't get it to tag the missing templates.
Any ideas would be much appreciated.
Jon
Solved! Go to Solution.
@Anonymous
Give this a shot
Template = IF ( OR ( Data[State] = "RESOLVED", SostenutoData[State] = "CLOSED" ), IF ( SEARCH ( "Find This Text!", Data[Resolution Detail],, 0 ) > 0, 10, -10 ), 0 )
Its because the way you have written SEARCH function, it will always return a number - either the string start position or zero so ISNUMBER is always true no matter what. Change the 4th parameter in SEARCH function from 0 to BLANK() and it should work.
Thank you very much, guys. Both solutions worked perfectly!
I found a third one this morning too; create a column in the query that uses matching to return a value. I prefer to do it this way though as I'm still finding my feet in Power BI and this was always reliable in Excel.
All the best, Jon
@Anonymous
Give this a shot
Template = IF ( OR ( Data[State] = "RESOLVED", SostenutoData[State] = "CLOSED" ), IF ( SEARCH ( "Find This Text!", Data[Resolution Detail],, 0 ) > 0, 10, -10 ), 0 )
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |