Remove trailing zeros only when there are three in a row

[1125NEWBIUSER]

Hi All,

I am trying to add a column next to current column that will take our customer numbers and show them as is unless it ends in three 0's and if it does, I want to show without only the trailing 3 0's. I have been using trusty google and got the below DAX but when I add to my new column, I get syntax . Can someone take a look and see what I am doing incorrectly? Thanks, Chris

[Keith-Sayer]

Hi there,

It looks like you are using M (which would work in PowerQuery but not in the Power BI Desktop) when it's expecting DAX. Try replacing the text.end with "Right," the text.start with "left," and the text.length with "LEN." Keep in mind, this only works if your data is a text string. Make sure it's not formatting it as a number.

Then try this:

IF(

    RIGHT('Customer Table'[Disp #],3) ="000",

    LEFT('Customer Table'[Disp #],LEN('Customer Table'[Disp #])-3),

    'Customer Table'[Disp #]

)

[deevaker]

Hi Chris,
You can replace your functions as below
text.end to RIGHT
text.start to LEFT
text.len to LEN

let me know if that works

Thanks,
Deevaker Goel

+91-9711975011
deevaker@hotmail.com
https://www.linkedin.com/in/deevakerg/

[JorgePinho]

Hi @1125NEWBIUSER 

Use LEFT() https://learn.microsoft.com/pt-pt/dax/left-function-dax instead of Text.Start

Use RIGHT() https://learn.microsoft.com/pt-pt/dax/right-function-dax instead of Text.End

Use LEN() https://learn.microsoft.com/pt-pt/dax/len-function-dax instead of Text.Lenght

Note: The -3 should be after the closing bracket of LEN() 

[1125NEWBIUSER · Accepted solution]

Thank you @JorgePinho,

I just did this and and now getting another syntex error. My customer ID's are not only numberic but some have text. I am now getting error about issues converting field to value. Is this needed for what I am even trying to do? Any hints? Below is message now. Thanks! Chris

[deevaker]

as @JorgePinho mentioned, please use -3 out of len function please

[1125NEWBIUSER]

Thank you, I missed that. By changing that did the trick!

[deevaker]

Great