Forum Discussion

MatjoCol's avatar
MatjoCol
Frequent Visitor
4 years ago

SQL Type UniqueIdentifier converted to text behaves differently in M vs DAX

I have two databases with a column Id in them. In one of the databases the Id column has type UniqueIdentifier. In the other database the type is varchar(50) as it contains ids in different formats. ...
General Comment
PhilipTreacy's avatar
PhilipTreacy
Super User
4 years ago

Hi MatjoCol 

 

I think what you are seeing is a result of the way DAX stores strings.  There's a good article about it here

 

Letter case-sensitivity in DAX, Power BI and Analysis Services - SQLBI

 

Basically DAX is case-insensitive and when it's storing strings, if it comes across anothjer string that is identical to one that it has already encountered, the second string is stored exactly the same as the first string.

 

So if it finds ABCD in your data first, then finds abcd, the 2nd string, abcd, actualy gets stored as ABCD.

 

So I suspect that DAX is finding your upper case ID first and storing all occurrences of it in upper case.

 

Regards

 

Phil

  • MatjoCol's avatar
    MatjoCol
    Frequent Visitor
    4 years ago

    PhilipTreacy 
    Thank you for your reply! 
    I read the article but I cannot see how it applies here. 
    Create the following example: 
    Query1 (connect to random database)

    SELECT CAST('0011661B-2AFC-4308-9550-1BC80576EDA1' as UniqueIdentifier) as Id, '2021-11-01' as Date1

     

    Query2 (connect to random database)

    SELECT CAST('0011661b-2afc-4308-9550-1bc80576eda1' as varchar(50)) as Id2, '2021-11-01' as Date2

     

    Import these, merge query1 with query2 on id = id2. This will look good in query editor. But you will get NULL on the join after you have applied it.