Search on Slicer in Power BI with ADX
Published Feb 08 2022 02:57 AM 1,310 Views
Microsoft

The Kusto connector is designed to create the most efficient KQL queries possible.

One of the tradeoffs it needs to consider is about searching for substrings in a text column.

In KQL there are many methods to search in text.

Four of this options can be used by the Power BI connector for searching in a slicer:

- has_cs - This is the default method and the more efficient one. It looks for full terms (words) within the text and is case sensitive 

- has - Less efficient because it is case insensitive but still will find only full terms

- contains_cs - Even more costly but will find any substring within the text and not just full terms. The search is case sensitive 

-contains - This is the most expensive method, it looks for substrings and is case insensitive

 

The difference in resource consumption between the different options is significant and can be X5 or even more.

On the other hand the default option is no good for searching on a slicer as you type a substring and expect to see the items that contains the substring.

What can you do?

 

One option is to use slicers on imported tables (aka dimensions)

You create relationships between these dimensions and the large fact tables with are using DQ and this filter the large table by selecting items in the imported ones.

Search will be done by Power BI and it will be the equivalent of using contains

This is a good option unless your dimensions are very dynamic and you need to use DQ.

 

Another option is to change the default behavior of the connector so that it will use contains_cs instead of has_cs or even contains if you want to allow for case insensitive searches.

In order to change the default you can use the connection properties [ForceUseContains=true] and [CaseInsensitive=true] , see Best practices for using Power BI to query and visualize Azure Data Explorer data | Microsoft Docs

Summary

Search values in slicers is a very important feature when the slicer contains many values.

If you use Direct Query with ADX, search will not bring any results as you type a search substring.

By changing the default settings, you can make search work as expected but you need to be aware  that the query will consume more resources

 

Co-Authors
Version history
Last update:
‎Feb 08 2022 02:57 AM
Updated by: