Issue related to Power BI DAX.

[v-rahyadav]

Text function in DAX return uppercase characters instead of the lowercase characters.

[steffens]

Thanks All,

You guys were very helpfull.  I consider this to be a  bug in Power BI that needs fixing. Our customers pay for their licenses and plan fees, so if the data is stored in upper or lowercase (no matter which occurs first), when retrieved or loaded it must be exactly the same

[Pragati11 · Accepted solution]

Hi @steffens ,

I don't consider this to be a bug in my opinion in Power BI. This is how the DAX engine works.

Also the values in the column are in different casing in the source data. It is not something that Power BI has added.

If the casing of the data displayed is of prime importance, then this needs to be handled at the time you load data into Power BI in Power Query editor - by changing the casing of your column using the following Transform option on your string column:

In this way you handle the casing issues in the source data in Power BI and make the data values more consistent from casing point of view - either all in lowercase or all in uppercase.

Thanks,

Pragati

[smpa01]

@v-rahyadav  DAX is case insensitive and probably M is the better choice for this kind of string manipulation.

[Pragati11]

HI @v-rahyadav ,

So, I tried MID dax expression on the following column that I have with just 4 values in it:

Scenario 1: DAX expression used to retrieve 1st character of every value in the column:

stringTest1 = 
MID(ClothingSales[Category], 1, 1)

This returns 1st character in uppercase as they are in my source data:

Scenario 2: DAX expression to retrieve 2nd character of every value in the column:

stringTest1 = 
MID(ClothingSales[Category], 2, 1)

This returns 2nd character in lowercase as they are in my source data:

So the DAX function seems to be working fine at my end.

#################################################################################

So basically, I would say DAX is not the problem. It is the way DAX engine works.

The DAX engine decides the casing of text alphabets based on the first casing of the text value it encounters in the column.

In your case, I bet the first value in your column has a UPPERCASE text value - so "B" is returned.

To test the hypothesis, just cnsider the values in the column below - so the 1st one has Uppercase B and 2nd one has Lowercase b:

But the DAX returned result in uppercase B.

Similarly, if I reverse the casing of first value in my column to lowercase and 2nd values to Uppercase:

The returned value using DAX is in Lowercase "b" - because DAX decided it based on the 1st text value casing that it encountered in the column.

You can read more about this on SQLBI website here: 

https://www.sqlbi.com/articles/letter-case-sensitivity-in-dax-power-bi-and-analysis-services/

So here DAX is not the issue, it is the way data is defined.

Thanks,

Pragati

[smpa01]

@Pragati11  try with same string, you will see

[Pragati11]

HI @smpa01 ,

In your scenario LEFT dax function returns everything in lowercase. The reason for this is the DAX engine.

The DAX engine decides the casing of text alphabets based on the first value it encounters in the column.

In your case, both the value only have ONE alphabet, i.e., "b" & "B". So, when you execute a DAX on this column the return value is decided by the value casing that appears first in the order - in your case it is LOWERCASE, so "b" is returned.

To test the hypothesis, just reverse the order of the values in your column - so the 1st one has Uppercase B and 2nd one has Lowercase b:

But the DAX returned result in uppercase B.

 You can read more about this on SQLBI here: 

https://www.sqlbi.com/articles/letter-case-sensitivity-in-dax-power-bi-and-analysis-services/

So here DAX is not the issue, it is the way data is defined.

Thanks,

Pragati