Case sensitivity in direct query // summarize vs treatas

[JYA]

Hi, 

I have a strange issue, and didn't found the right keywords to find approaching answer 😕

I tried to reproduce the issue with a standard table to share, but powerBI remove case sensitivity directly so I was not able to, issue is with a directquery.

To simplify, let's suppose I have a table "myTable" with for instance columns "Category" and "Value"

In "Category" I have in particular 2 strings like 'my text' and 'my Text' that differs only by one character being upper case.

myTable :

Category	Value
my text	1
my Text	2

If I create a summarized table (used for double selection) :

Summary Table = SUMMARIZE('myTable', 'myTable'[Category])

Summary Table : 

Category

my text

>>> The summary table keep only 'my text' (powerBI optimisation).

That is by the way not an issue for me at this stage, the case difference should not be taken into account in my case.

if now I try to filter myTable based on this summary table :
    measure with issue = 
    var s = SUMMARIZE('Summary Table', 'Summary Table'[Category])

    return CALCULATE(SUM('myTable'[Value]), TREATAS(s, 'myTable'[Category])

>>> Return 1

This sum will not take into account the values associated to 'my Text'... I will get 1 instead of the expected 3...

If I do not go through an intermediate generated table, but summarize directly in the measure I do not have the issue, probably
because powerBI does not go through the optimisation process for storage in this case. 
I have not found yet a workaround to solve this, but I guess the issue is that if powerBI remove case sensitivity to store while TREATAS does not benefit of this optimization and keep case sensitivity... 
If anyone has a hint 😥 or highlight if I misundertood something?

Thanks in advance!

[lbendlin · Accepted solution]

This difference between Power Query and Power BI has been a long standing issue.  Either 'correct'  your source data to remove the different spellings ( Case insensitive Table.Distinct (microsoft.com) ), or add information to preserve the case sensitivity in DAX (similar to the "Case Safe ID"  concept of Salesforce.com)

[JYA]

Thanks a lot to confirm the issue!

I have requested to the admin of the dB to remove the different spelling, as I'm using direct query. 

I will give a try to add a column as I'm using a composite model but I fear issue on performances...