REPLACE Function Returning Unexpected Results

[tamerj1]

@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

[SpartaBI · Accepted solution]

@tamerj1 so, becasue until someone else will say otherwise, you found a bug 🙂
In the mean time, I just jumped over it and solved it in another way (I'm adding the DAX Query):

EVALUATE 
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, 
			"@Extracted Letter", 
			MID ( 
				[@Title], 
				[@LetterLocation],
				COALESCE(
					MINX( 
						FILTER(
							T6, 
							[@LetterLocation] > EARLIER([@LetterLocation])
						),
						VALUE([@LetterLocation])
					),
					_Length * 3
				) - [@LetterLocation] - 1
			)
		)


VAR T8 = ADDCOLUMNS ( T7, "@Extracted Letter Title Case", REPLACE ( [@Extracted Letter], 1, 1, [@CapitalLetter] ) )


VAR Result = 
    { 
        CONCATENATEX ( 
            T8,
            [@Extracted Letter Title Case],
            " ",
            [@LetterLocation],
            ASC
        )
    }
RETURN
    Result

Showcase Report – Contoso By SpartaBI

     

[tamerj1]

@SpartaBI 
This is amazing! splitting the sentence into words bypassed the the "bug" 🙂 Great job my friend!!!

As small problem when having a single word. The extraction seems to miss the last letter. I guess you can solve that. Otherwise it works perfect! 

On the other hand I hope DAX experts can confirm if this is really a bug or this is just a DAX behaviour which we could not understand. I Don't want to be rude but @AlbertoFerrari  and @marcorusso who's more competent than you guys to answer this question. 

[SpartaBI]

@tamerj1 my pleasure my friend 🙂 I was very happy I had a chance to help you.
I really appreciate your skills.
Regarding the one word, so basically it's just the scenario when the coalesce kicks in, so just need to have there a large enough number. I'm editing the code now in the original message and changing it to be _Lentgh * 3

Showcase Report – Contoso By SpartaBI

      

[tamerj1]

@SpartaBI 

I'm here just to learn. It is amazinghow much a person can learn just trying to answer questions and reading other people answers. I heard about DAX last August and strted baby steps with Power Pivot two months later. My first experience with Power Bi was about 3 months ago.

Thank you again you are really amazing! And there slot to be learned from this small piece of code youve added. 
FYI I have no business with data analysis whatsoever. I'm just a mechanical engineer working at construction sites but I found DAX interesting. 

[SpartaBI]

@tamerj1 well, I can tell you for sure, that you have a natural talent for DAX and I'm pretty sure you can be a top notch data analyts and/or data developer whenever you will want to :).
Have you read the definitive guide to dax? Basically just read that and/or take their course and watch all their videos and you will be one of the best in the world for sure.

[tamerj1]

@SpartaBI ❤️

I read every single page and still referring back to it almost everyday. I have a gold fish memory 😅

I watched every single video in SQLBI youtube channel and all the free tutorial in SQLBI website. I think my advantage is that I'm not from IT background and I've never learned before any other programming language. So I guess my mind was not presett based on typical programming languages. 

[SpartaBI]

@tamerj1 I think your advantage is that you are just really smart and talented 🙂

[tamerj1]

@SpartaBI thank you so much. You made me feel proud of myself ☺️
One more thing I like add that a thing has to matter in order to be interesting. I could not realize how much dax can be important and handy until I watched the SQLBI Patterns series which made my eyes wide open to the infinite possibilities of what dax can do. 
Thank you again I really enjoyed chatting with you. 

[SpartaBI]

@tamerj1 my pleasure 🙂
We definitely share the same ❤️ for DAX.
I also enjoyed our conversation and hopefully will do it again 🙂

[tamerj1]

Thank you @SpartaBI Really appreciate your help.
I just got back to office. Let me check and get back to you.

[SpartaBI]

@tamerj1 just to be sure, what is the result you wan to get from:
set field title case
Is it:
Set field title case or Set Field Title Case
?

[tamerj1]

Thank you @SpartaBI for your response.

I want to get Set Field Title Case

I gues there might be more simple solutions. This for sure will be of my intrest. However, it is now more interesting to me to understand why does this function behave this way! What was the mistake which I was not able to spot?!

[SpartaBI]

@tamerj1 ye, I got now to T7 and looking at the bug haha
Indeed just the fact that it doens't work with 
REPLACE ( [@Title], [@LetterLocation], 1, [@CapitalLetter] ) )
But works with
REPLACE ( [@Title], [@LetterLocation] + 1, 1, [@CapitalLetter] ) )
looks like some interesting edge case 🙂