cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to capitalize the first letter of each word in DAX

 Text Calculated Column how_are_you How Are You welcome_to_power_bi Welcome To Power Bi

Hi Guys,

I am new to power bi. I need to create the calculated column as shown in the above table. Thanks in Advance

@Anonymous @PowerBI

1 ACCEPTED SOLUTION
Super User

@Anonymous Here is a complete DAX solution that capitalizes each word:

``````Column =
VAR __Separator = "_"
VAR __SearchText = 'Capitalize'[Text]
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
)
RETURN
CONCATENATEX(__Table,[__Replace]," ",[Value])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
11 REPLIES 11
Super User

@Anonymous Here is a complete DAX solution that capitalizes each word:

``````Column =
VAR __Separator = "_"
VAR __SearchText = 'Capitalize'[Text]
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
)
RETURN
CONCATENATEX(__Table,[__Replace]," ",[Value])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

The above code which you given is working fine for import mode. But it's not working for the Direct Query Mode. My source Data is Microsoft SQL Server. Thanks

Super User

@Anonymous Do you know what function is failing in DirectQuery mode? I just created this Measure version, there is only a single change. I tested this in Direct Query mode against the AdventureWorks database and it worked just fine.

``````Measure =
VAR __Separator = "_"
VAR __SearchText = MAX('Capitalize'[Text])
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
)
RETURN
CONCATENATEX(__Table,[__Replace]," ",[Value])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

What I did means. I added a query to the measure and then added the measure to the table chart. It shows me an error which I was uploaded as a picture.

Here I added a query to the calculated column. It could not identify some functions. These two issues I was facing.

Super User

@Anonymous Right, as a calculated column in a Direct Query model, half the DAX functions aren't going to work. I don't understand the issue with the measure as I tested this specifically against a Direct Query model. Please paste the full text of your DAX measure and I can take a look. Seems like you have something incorrect in your RIGHT function call.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

``````Measure =
VAR __Separator = "_"
VAR __SearchText = MAX('ValuationProcedureData'[AssetStatus])
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
)
RETURN
CONCATENATEX(__Table,[__Replace]," ",[Value])``````

Super User

@Anonymous I am guessing that it is a boundary case where there is only a single word. Going to check.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@Anonymous Yep, that was it, specifically a single letter followed by an _ Try replacing the "__Replace" line of your code with this:

``````            "__Replace",
VAR __Rest = LEN([__Word])-1
RETURN
IF(__Rest<=1,UPPER(LEFT([__Word],1)),UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi,

In case you want to use DAX then try this

Column = UPPER(SUBSTITUTE(Sheet2[Text], "_", " ") )

Resolver II

Hi,

in power query editor > Transform Tab > Format.  Then select uppercase.

Right click on rown and select remove values - remove _ and replace with " " .

Super User

Hey @Anonymous ,

that's difficult in DAX but easy in M/Power Query.

Just go to Power Query, replace underscore with a space and use the "Capitalize Each Word" function:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.