Home
Training
Consulting
Articles & Blog
Videos
Books
Tools
About

Menu
Search
0
LOGIN

Home
Training
Consulting
Articles & Blog
Videos
Books
Tools
About

Accept cookies to show this video. Cookies Policy
Allow all cookies

REPLACE – DAX Guide

REPLACE: Replaces part of a text string with a different text string.
https://dax.guide/replace/

Apr 15, 2021

LinkedIn Twitter Facebook Email

Subscribe

Comments

You need to login in order to comment this video.

Login Or register

Subscribe

Notify of

Please login to comment

1 Comment

Inline Feedbacks

View all comments

Tamer Juma

2 years ago

Hi Alberto

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://www.dropbox.com/t/elOnTaavabF8cJVJ

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

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 Alberto help me sleep again.

Best of the best regards,
Tamer

1

StickClose

All

12:47

Differences between ALL and ALLCROSSFILTERED

Jan 28, 2025
45:22

Optimizing incremental inventory calculation in DAX

Jan 14, 2025
05:16

What happened in 2024

Dec 30, 2024
25:11

Reusing formatting for Power BI reports

Dec 17, 2024
07:01

Detect and Fix Issues with Tabular Editor 3 and DAX Optimizer Integration

Dec 9, 2024
40:08

BLANK in date columns and DAX time intelligence functions

Dec 3, 2024
08:07

Real-World Use Case for DAX Optimizer: Real Estate

Dec 2, 2024
30:25

Compare DAX Optimizer vs ChatGPT vs Copilot for Power BI

Nov 25, 2024

Newsletter

By subscribing you are agreeing to our Privacy Policy.

246.0K subscribers

Get BI news and original content in your inbox every 2 weeks!
Newsletter archive

What is next

Mastering DAX – Feb 11
, Amsterdam
Mastering DAX – Feb 18
, Phoenix
Mastering DAX – Feb 24
, Dallas
Data Modeling for Power BI – Apr 14
, Amsterdam

YouTube Twitter LinkedIn Facebook Github RSS Feed

Marco Russo Alberto Ferrari Daniele Perilli

2004-2025 © SQLBI. All rights are reserved.
SQLBI is a trademark of SQLBI Corp.
Loader is a European legal entity.

Privacy Policy & Cookies Terms & Conditions Contact Us

You are going to send email to

Move Comment