TRIM the data from a measure

[adoalan]

Hi

I have this forumla to trim the data but I have one item which I still need to remove '-U'.

How can I include that in the same formula let's say to trim also the data where '-U' is identified.

Only measure please because I cannot import all tables in Power BI and use custom columns and power query.

[PhilipTreacy · Accepted solution]

Hi @adoalan 

Change the RETURN statement to this

RETURN SUBSTITUTE(MID(EvalString, 1, LenTo), "-U", "")

Regards

Phil

[adoalan]

@PhilipTreacy 

Actually there is an error. Instead of getting one line like below

I get this

When I remove the formula

Is giving the correct results.

Any idea how to fix this issue?

Thanks

[PhilipTreacy]

Hi @adoalan 

Sorry I'm not seeing what the problem is.  What do you want as the result?

regards

Phil

[adoalan]

Hi @PhilipTreacy 

The result should be just one line in the example which I posted below But somehow the formula is duplicating some values.

For example if I put a filter on ADDC06-01/002-UK

On the report without formula added I get only one line eith the amount of 63.

But when I drag the formula in I see all those with different Codes, the same sourcecode and the same amount

Let me know if makes sense

Thank you

[PhilipTreacy · Accepted solution]

Hi @adoalan 

The 2nd image shows that you have many different Codes in your table, so when you are only seeing 1 row when the Final 1 measure isn't included, this may be because of some other filtering or aggregation on that visual.

The measure isn't duplicating data, that data is there, it's just not shown in your first visual.

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @adoalan 

Change the RETURN statement to this

RETURN SUBSTITUTE(MID(EvalString, 1, LenTo), "-U", "")

Regards

Phil

[adoalan]

Thank you @PhilipTreacy 

Worked perfectly.