topic Re: I think I broke DAX again, bug in CONCATENATEX?? in DAX Commands and Tips

I think I broke DAX again, bug in CONCATENATEX??

Greg_Deckler — Thu, 21 May 2020 04:52:43 GMT

OK, this is bizarre but there is some strange behavior going on with CONCATENATEX. Steps to recreate:

1. Create the following tables in Power Query, Table, Table2 and Table3:

Table:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFOK1QEySnMyU8GsgMRcpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}) in #"Changed Type"

You will have in your table:

John

Julie

Pam

Table2:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlWK1YlW8srPyIMwSnMyIUIBiblKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}) in #"Changed Type"

You will have in your table:

Mike

John

Julie

Pam

Table3:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFOK1QEySnMyU8GsgMRcMO2bmQ0UiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}}) in #"Renamed Columns"

You will have in your table:

John

Julie

Pam

Mike

2. Now create the following column in each table:

Name Again = VAR __Value = [Name] VAR __Table = ADDCOLUMNS( GENERATESERIES(1,LEN(__Value),1), "Char",MID(__Value,[Value],1) ) RETURN CONCATENATEX(__Table,[Char],,",")

In Table, "Name Again" column for Pam is "Pam".

In Table2, "Name Again" column for Pam is "PaM"

In Table3, "Name Again" column for Mike is "mike"

So, it is as if somehow CONCATENATEX is "remembering" the value of lower or upper case letter from a previous iteration?????

marcorusso or anyone else you can explain?????

Re: I think I broke DAX again, bug in CONCATENATEX??

nandukrishnavs — Thu, 21 May 2020 06:07:37 GMT

Greg_Deckler

Look at this 😀

Re: I think I broke DAX again, bug in CONCATENATEX??

nandukrishnavs — Thu, 21 May 2020 10:46:06 GMT

Greg_Deckler

Try this

Table = VAR __Value1 = "NanduKRISHNA" VAR __RESULT = ADDCOLUMNS ( GENERATESERIES ( 1, LEN ( __Value1 ), 1 ), "Char", MID ( __Value1, [Value], 1 ) ) RETURN __RESULT

Create another Table

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDSAUBDdhkDtsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}) in #"Changed Type"

Then create a calculated column

Char = VAR __Value = "NanduKRISHNA" VAR __result = MID ( __Value, [Value], 1 ) RETURN __result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: I think I broke DAX again, bug in CONCATENATEX??

marcorusso — Thu, 21 May 2020 10:26:20 GMT

DAX is not case sensitive, and usually, I consider this for the data stored in a column, like in the last calculated table.

But this case is curious because it demonstrates that the formula engine applies this also to cases where the result is "unexpected".

You can reproduce the issue with a single DAX query without storing anything in a single column.

EVALUATE ADDCOLUMNS ( SELECTCOLUMNS ( { "Mike", "Pam" }, "Name", [Value] ), "Name Again", VAR __Value = [Name] VAR __Table = ADDCOLUMNS ( GENERATESERIES ( 1, LEN ( __Value ), 1 ), "Char", MID ( __Value, [Value], 1 ) ) RETURN CONCATENATEX ( __Table, [Char] ) )

The result is

Mike	Mike
Pam	PaM

I suspect that the string created internally to the formula engine for Pam (letter "m") reuse an existing string already created for Mike ("M") because in a case-insensitive world, it is the same string.

Clearly, this is not ideal in this case, even though expected when you import data.

New lesson learned today!

Re: I think I broke DAX again, bug in CONCATENATEX??

nandukrishnavs — Thu, 21 May 2020 10:44:29 GMT

marcorusso Greg_Deckler

I have created a table in EditQuery.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymgklHMOkEJp3BpAuYdFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Char = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Char", type text}}) in #"Changed Type"

But when I look at the same table in the Data view, it was surprising.

Re: I think I broke DAX again, bug in CONCATENATEX??

marcorusso — Thu, 21 May 2020 10:47:33 GMT

Not surprising, it is by design. Power Pivot always worked this way, Power BI too.

Re: I think I broke DAX again, bug in CONCATENATEX??

Greg_Deckler — Thu, 21 May 2020 12:36:11 GMT

Whoa, whoa, wait a minute, it is "normal" behavior for DAX/Power BI to change the case of imported data???

Re: I think I broke DAX again, bug in CONCATENATEX??

marcorusso — Fri, 22 May 2020 12:46:13 GMT

Unfortunately, yes. The first string imported keeps its case, following strings that are different only for the case use the previously imported string. It has been so since Power Pivot in 2010.

Re: I think I broke DAX again, bug in CONCATENATEX??

Greg_Deckler — Fri, 22 May 2020 13:45:49 GMT

marcorusso nandukrishnavs , sorry, still trying to wrap my head around this, I have confirmed this behavior for things like:

PAM

pam

Both get: PAM

And

This is A SenTence

tHIS is a SENTENCE

Both get: This is A SenTence

It just strikes me as a serious data quality issue, I can't imagine a case where I would want my data to be munged and effectively corrupted like this outside of my control. It just seems fundamentally wrong.

Re: I think I broke DAX again, bug in CONCATENATEX??

nandukrishnavs — Fri, 22 May 2020 14:26:13 GMT

Greg_Deckler

Can you think about to use UNICODE() and UNICHAR() functions in your actual logic?

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: I think I broke DAX again, bug in CONCATENATEX??

marcorusso — Fri, 22 May 2020 14:31:19 GMT

The reality is that it could be changed because it depends on the Analysis Services collation you choose during setup. However, Power BI and Azure Analysis Services don't allow any change, so the de-facto standard is now case insensitive.

You must live with it and there is zero chance this could change in the future because of regression and compatibility issues.

I was worried the same way when I discovered this in Power Pivot, but I have to say that the number of real-word issues caused by this behavior so far is zero, in my personal experience. I'm not defending the choice, I'm just saying that it doesn't worth spending too much time on it.

Re: I think I broke DAX again, bug in CONCATENATEX??

nandukrishnavs — Fri, 22 May 2020 14:39:56 GMT

Greg_Deckler

Table1 = VAR __Value1 = "This is A SenTence" VAR __RESULT = ADDCOLUMNS ( GENERATESERIES ( 1, LEN ( __Value1 ), 1 ), "unicodeVal", UNICHAR(UNICODE(MID ( __Value1, [Value], 2 ))) ) RETURN __RESULT

Note: I have specified 2 instead of 1 in MID() to intentionally get the case sensitive text. Then Unicode() will return the Unicode of the first text only. Then converted to the actual character using UNICHAR().

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂