Model Joins on Strings Error

[DH_UOW]

Hi,

I have just tried to create a concatenated composite key style join in a model.  Those strings are case sensitive.

Unfortunately when the strings are brought into the Power BI model and used to create a join the behaviour of Power BI to collate strings with the same characters but different case means that in my dimension the keys are being flagged as having duplicates.  Also on the fact side of the join the keys are incorrect.

The field also cannot be flagged as being a Key Column, as it's already been changed, before the user is able to tell Power BI to treat it as a key.  Logically the change shouldn't happen prior to understanding what fields to use as a key.

While may be "nice" that a dimensional attribute is collated (though it can also be incorrect), it does not make logical sense to do this for a key value used in a join.

Note:  I understand that a workaround is to create a surrogate key rather than the concatenated composite key, but this is a logic error that really shouldn't exist in a BI tool, and shouldn't have to be worked around.

Example Data:

key,attribute
123_a_b,Fred
123_b_b,Wilma
123_A_b,Betty
123_B_b,Barney

In Power Query:

In Power BI Data Viewer:

Trying to define a join in the Model:

Try to define the key column as unique:

[amitchandak]

@DH_UOW , check f these two can help in resolving

https://stackoverflow.com/questions/68476720/power-bi-column-value-being-converted-to-lower-case

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

[DH_UOW]

The second one is related and I have read that while trying to work out what is happening.  It's just another workaround to a broken model of treating all string values as case-insensitve.  Users are left to try and find workarounds to meet their scenario (read the comments on that post to see what this behaviour is doing to URL's etc).

This is an issue caused by DAX treating strings as case-insensitive values, rather than just leaving them alone.

The stack overflow post doesn't have any offered solution.

As I say there is a workaround of creating a surrogate key, but that means modifying the data warehouse models and not going to be a simple job.  Another work around for my scenario is to create a binary rendition of the string in M using Text.ToBinary() and to do that on both sides of the join before the tables move into the model.

However, this fundementally is a bug that should be fixed in the tool, rather than being left to the community to find workarounds, that meet some but not all scenarios.