Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
50 | |
39 | |
18 | |
14 | |
13 |
User | Count |
---|---|
99 | |
67 | |
28 | |
18 | |
13 |