cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## DAX Extract Text Before Delimeter Error when Subtracting

L1 HOME PROJECT =
-- Example Project Code 1 12345.123.12.1000 Attempting to extracted 12345
-- Example Project Code 2 12346.123.12.1000 Attempting to extracted 123456
VAR Home_Poject =
RELATED ( EMP_LOOKUP[HomeProject] )
VAR L1_Home_First_Period =
SEARCH ( ".", Home_Poject, 1, BLANK() ) - 1 -- this is what is trying to be accomplished,
--find the first period and subtract one.
--However, it provides the following error...
--MID has the wrong data type or has an invalid value
// it will add but not subtract

VAR L1_Home_First_Period_not_Subtracted =
SEARCH ( ".", Home_Poject, 1, 0 ) --this works but brings in the period, 12345. 123456. Not sure how to reduce it by one

RETURN
MID(Home_Poject,1,L1_Home_First_Period_not_Subtracted)
-- L1_Home_First_Period_not_Subtracted - 1

Thank you in advance
3 ACCEPTED SOLUTIONS
Super User

@Anonymous Perhaps you need to check if it is blank or 0?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver III

You must have a value without any "." in it. Then SEARCH ( ".", Home_Poject, 1, BLANK() ) will return 0 and the negative value will throw the calculation off.

You can add an if statement to handle the 0, something like if search() = 0 then 0 else search()

Community Support

Hi @Anonymous

you can try this measure

Measure = LEFT(SUBSTITUTE(MIN('Table'[Column1])," ","."),SEARCH(".",SUBSTITUTE(MIN('Table'[Column1])," ","."))-1)

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @Anonymous

you can try this measure

Measure = LEFT(SUBSTITUTE(MIN('Table'[Column1])," ","."),SEARCH(".",SUBSTITUTE(MIN('Table'[Column1])," ","."))-1)

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Resolver III

You must have a value without any "." in it. Then SEARCH ( ".", Home_Poject, 1, BLANK() ) will return 0 and the negative value will throw the calculation off.

You can add an if statement to handle the 0, something like if search() = 0 then 0 else search()

Super User

@Anonymous Perhaps you need to check if it is blank or 0?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...