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.
Hello! I am new to PowerBI.
I need to have a calculated column how to get the number of characters from a certain column(JobType_txt). However, there is one condition. Some of the Jobtype_txt dont have a value in # of _ Column. See screenshot below.
Condition is: If # of _ is " " then Phase_=" " else need a calculation for below requirement.
Example: Jobtype_txt: NC 110_Estimates__10NotStarted
The text is divided into
Prefix: NC 110
"_"
Phase_:Estimates_
"_",
Status:10NtotStarted
So, I need to have a calculated column the number of characters from Prefix until "Phase_". So from JobType_text: NC 110_Estimates_.
So the Phase_=17
I have this sample Excel formula for this. See screenshot below.
My problem is I don't know how to convert this to DAX formula.
I hope you can help me on this.
Thank you.
Solved! Go to Solution.
Hi @juhoneyighot,
You can try to use following formula to create a calculated column to get the character counts:
charCount =
VAR _path =
SUBSTITUTE ( 'Table'[Text], "_", "|" )
VAR _suffix =
PATHITEM ( _path, PATHLENGTH ( _path ) )
VAR replaced =
SUBSTITUTE ( 'Table'[Text], "_" & _suffix, "" )
RETURN
LEN ( replaced )
Regards,
Xiaoxin Sheng
Hi @juhoneyighot,
You can try to use following formula to create a calculated column to get the character counts:
charCount =
VAR _path =
SUBSTITUTE ( 'Table'[Text], "_", "|" )
VAR _suffix =
PATHITEM ( _path, PATHLENGTH ( _path ) )
VAR replaced =
SUBSTITUTE ( 'Table'[Text], "_" & _suffix, "" )
RETURN
LEN ( replaced )
Regards,
Xiaoxin Sheng
Does it have to be DAX? You can use the PATH functions for that.
It's much easier to do in Power Query.
Please provide sample data that fully covers your issue. Are there two underscores as the second divider?
Please show the expected outcome based on the sample data you provided.
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |