REPLACE Function Returning Unexpected Results

AlbertoFerrari marcorusso and Comunity

This is my first question in the Power Bi Comunity and I realy need your help. Any input would be highly appreciated.

Usually I prefer to search, study and find answers by myself. This time I give up. I searched everywhere and I could not find an answer to my problem. Please allow me to explain in some details and I hope you will be able to read this and provide an answer.

I was writing a DAX code that is supposed to capitalize the first letter of each word. For some reasons, SQL and PQ are not an option and DAX is the only option. I had an idea in my mind and started writing the code and everything was going smooth as planned. Except when I reached the part where I needed to use the EVIL function REPLACE and his EVIL twin SUBSTITUDE

The goal was to create a calculated column, however, I also created a calculated table just to see how the code behaves step by step.

Here is a sample file https://we.tl/t-yN1a77H4fP

And this is the DAX code

Title Case = 
VAR CurrentTitle = SELECTCOLUMNS ( FILTER ('Table', 'Table'[Title] = "set field title case" ), "@Title", [Title] )
VAR Length = LEN ( CurrentTitle )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Title", CurrentTitle, "@SapceLocation", FIND ( " ", CurrentTitle, [Value], BLANK ( ) ) )
VAR T3 = FILTER ( T2, [Value] = 1 || [@SapceLocation] = [Value] )
VAR T4 = ADDCOLUMNS ( T3, "@LetterLocation", IF ( [Value] = 1, 1, [Value] + 1 ) )
VAR T5 = ADDCOLUMNS ( T4, "@FirstLetter", MID  ( [@Title], [@LetterLocation], 1 ) )
VAR T6 = ADDCOLUMNS ( T5, "@CapitalLetter", UPPER ( [@FirstLetter] ) )
VAR T7 = ADDCOLUMNS ( T6, "@TitleCase", REPLACE ( [@Title], [@LetterLocation] + 1, 1, [@CapitalLetter] ) )
VAR Result = 
    { 
        CONCATENATEX ( 
            T7, 
            VAR CurrentLocation = [@LetterLocation]
            VAR PreviousLocation = MAXX ( FILTER ( T7, [@LetterLocation] < CurrentLocation ), [@LetterLocation] )
            VAR NumOfCharachtors = CurrentLocation - PreviousLocation - 1
            RETURN
                MID ( [@TitleCase], [@LetterLocation], NumOfCharachtors ),
                " ",
                [@LetterLocation],
                ASC
        )
    }
RETURN
    Result

Attached screenshots explains everything

In the first screenshot you can see the REPLACE is stuck!. It only replaces the first letter of the first word with the first capital letter. It is like ADDCOLUMNS is not iterating over the table and is just returning the first result over all the rows!

In the 2nd screenshot I just added (+1) and now the code works perfectly except that it replaces the wrong letters.

I even tried to change the column values manually, using other columns and creating new columns, it ALWAYS works perfectly except when it should produce the result that I want!!!

Also I tried creating index column and use it in SUBSTITUTE function, tried with a calculated column and even a measure and the results are even worse.

I searched every where just to understand how this function really works and why DAX can sometimes be so mean and evil. Unfortunately, could not find the answer. Please help me sleep again.

Best of the best regards,
Tamer