Why slicer search sometimes work & sometimes not & sometimes, it works again (DirectQuery)

[dibaSFP]

Hi eveyone,

as we are currently asking MS support to actually fix the default slicer search feature (even after the recent improvements), I would like to give you a hint, why the slicer search feature may act weird.

The actual problem and some good, but not perfect solution is described here: https://community.powerbi.com/t5/Service/How-to-make-Search-option-as-Case-insensitive-in-Power-BI-s...

The problem:

• First, you need a case-sensitive database and - I guess - a DirectQuery data source
• Then, let's assume you have the values "Example" and "ExampleFoo" as slicer values

First round:

1. Open the PBI report
2. If you search for "Exa" you will see both values
3. If you search for "exam" you will still see both values
4. Now search for "ex" ... you get nothing

Close the report and start over:

1. Open the PBI report
2. If you search for "exa" you get nothing
3. If you search for "Exam" you still get nothing
4. Now search for "Ex" and you will see both values

So, it seems to be erratic.

Actually, the problem is a case-insensitive cache with an "advanced" logic: If you search for a longer term ("Exam") than before, then PBI recognizes, that you already searched for a similar shorter term ("exa") which did not bring any results. So, it even does not ask the database again for the longer term.

So, each step from above can be explained:

1. If you search for "Exa" you will see both values:
   Query request: You get results, as the search term case matches the database values
2. If you search for "exam" you will still see both values:
   No new request: But as you already searched for "Exa", the values are known
3. Now search for "ex" ... you get nothing:
   No cache on this. So, we issue a new query request - with no results, as the case does not match

Second round:

1. If you search for "exa" you get nothing:
   Query request send, no results, as the case does not match the database values
2. If you search for "Exam" you still get nothing:
   You already searched for "exa" (duh!) with no results, so why should I send a new request?
3. Now search for "Ex" and you will see both values:
   No cache on this. So, we issue a new request - getting the values, as the case match

Currently, the valued MS support claims that it is a driver issue, but obviously, it is a problem of the visual (and/or the caching behind it).

• IMHO, the best solution would be, if the search feature would search "case insensitive" and everything would be fine.
• Alternatively, the search caching could be case sensitive. For a search feature this may not ideal, but at least, the search functionality would not seem to be erratic. To be fair: In case insensitive databases (e.g. SQL server, if not configured differently) your caching would be less effective, as searching for "exa" and "Exa" would both trigger a request (but returning the same values).

If you have the same problem (slicer search feature, case sensitive DB, Direct Query), please contact support and file an issue. Thx.

Regards,

DibaSFP

[Greg_Deckler]

@dibaSFP Would make an excellent blog article.