DAX Extract Text Before Delimeter Error when Subtracting

[Anonymous]

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 

[Greg_Deckler · Accepted solution]

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

[Arentir · Accepted solution]

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()

[v-xiaotang · Accepted solution]

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.

[Arentir · Accepted solution]

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()

[Greg_Deckler · Accepted solution]

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