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,
Need help with my DAX. I need to be able to extract just the "Sprint ##" part from my text field
My text field format can be like any of the below examples
Team_Name\Project Name\Queue Name\Sprint 1
Team_Name\Project Name\Sprint 10
Team_Name\Queue Name\Sprint 35
Team_Name\Sprint 54
As you can see the number of backslashes changes, so I created the below code to turn the right most backslash into an "@", so that then I could use MID and FIND to identify and split the "Sprint ##" part out
IP_Sprint =
SUBSTITUTE(SELECTEDVALUE('View for PBI Report'[Iteration Path]),"\","@",
LEN(SELECTEDVALUE('View for PBI Report'[Iteration Path]))
-LEN(SUBSTITUTE(SELECTEDVALUE('View for PBI Report'[Iteration Path]),"\","")))
The above code works but I get the below error when I try to do MID(IP_Sprint,FIND("@",IP_Sprint,1)+1,10)
Can anyone please help me resolve the issue
Solved! Go to Solution.
Hi @Anonymous It seems some kind of Bug in system. Find function is working fine if we are giving optional 4th parameter for "NotFoundValue" value. Please try below. Change "-1" as per you deem fit.
https://docs.microsoft.com/en-us/dax/find-function-dax
MID([IP_Sprint],FIND("@",[IP_Sprint],1,-1)+1,10)
Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.
@Anonymous
Refer
https://community.powerbi.com/t5/Desktop/Remove-last-occurence-of-specific-character/td-p/81354
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @Anonymous It seems some kind of Bug in system. Find function is working fine if we are giving optional 4th parameter for "NotFoundValue" value. Please try below. Change "-1" as per you deem fit.
https://docs.microsoft.com/en-us/dax/find-function-dax
MID([IP_Sprint],FIND("@",[IP_Sprint],1,-1)+1,10)
Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.
@Anonymous
Hey @Anonymous ,
maybe you can adapt this formula to your needs:
PATHITEMREVERSE(SUBSTITUTE('Table'[the string] , "\" , "|") , 1 , TEXT)
The function SUBSTITUTE replaces all "\" with "|". The pipe sign | is used as a separator by all the PATH... functions. Then PATHITEMREVERSE takes the last item, by counting backwards.
Regards,
Tom
Hi @Anonymous
Please see the attached file with few options for you to choose.
Measure, DAX Column and Power Query Column ( my prefered option )
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |