How to make Search option as Case insensitive in Power BI slicer (Direct query)

[Anonymous]

Hi,

I am using direct query approach in my report. Search option is case senstive in my report

how to make this search option as case insensitive in my report ?

I have read this Article but not sure how to solve my issue

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

[BarryASeymour]

I have a workaround but it's ugly. I'm using directquery, so I modified my SQL to return an additional column that's converted to upper case. I don't show it in the results, but I use a slicer to search on it. Unfortunately my users have to type their search terms in upper case as well for it to work.

Trying to find a way to capture and convert the user's input before it's passed through to the query. I get tantalizing hints from CoPilot, but I can't make any of them work.

[mbahonen]

Thanks, yeah I'm not willing to go to those lengths yet.  This is definitely something that MSFT should make available out of the box.

[Anonymous]

Hi @Anonymous,

Since you are working with the direct query mode, I'd like to suggest you consider using T-SQL statement to append a custom field that used to upper/lower case the values of the specific fields.
After these steps, you can directly search for new fields without worry about case sensitive scenarios.
Regards,

Xiaoxin Sheng

[collinq · Accepted solution]

Hi @Anonymous ,

Power BI is always case sensitive to my knowledge.  There is an idea to change this here:

https://ideas.powerbi.com/ideas/idea/?ideaid=a9843990-64f6-ea11-b5d9-281878bdaa58

I use two different work-arounds for this.  I will make the entire column one case (all uppercase) and that solves it.  But, that isn't always the way I want to the data to be presented so sometimes I will create a second field, make it all uppercase, use that new field for the search and then only display the original version.  Again, not perfect but it works in some situations.

I also found this article that gets around it with some M code:

https://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[mbahonen]

That issue is still unaddressed unfortunately. I've upvoted it, but its crazy MSFT hasn't added case insensitivity to slicers/filters text searches.