How to force filtering on at least one criterion when querying ADX data in direct query mode

[Guest DanyHoter]

How to force filtering on at least one criterion

 

Another case for using dynamic M parameters and functions

 

Scenario

 

 

 

 

Recently I encountered the following issue working with a customer:

 

• There are two main ways to slice the data before visualizing it in a PBI report.
  
• The user can filter by one column or two columns coming from two different dimension tables.
  
• If there is no selection on any of the two columns, the queries fail on lack of resources to perform all the joins and summaries.
  
• While moving from filtering on one column to filtering on the other column, it is very natural to move through a state in which both filters are open, and the queries are very expensive and eventually fail.
  
• The goal was to prevent these cases and not to attempt a query with no filtering.
  

 

We need to allow multiple selection and also allow for selecting all values in any of the slicers, but we can’t require selection in at least one of the two.

 

We could create a measure that will return blank if no selection is applied but this will not prevent the query from being executed to calculate the list of items on the visuals.

 

Solution

 

 

Using data in the help cluster, we’ll create a PBI report that will demonstrate a solution to our problem.

 

The data volume is not so big so the report will return values even if no selection is applied but we want to prevent this kind of query and force a selection on one filter at least.

 

The two columns used are cities and colors in the two dimension tables Customers and Products respectively.

 

We start by creating a function with two parameters, one with a list of cities and one with a list of colors.

 

The function returns all rows that fit the criteria.

 

A special value will be sent in the parameters if no selection was made, or all values are selected.

 

 

 

 

 

 

 

The function

 

 

 

 

.create-or-alter function FilterColorsCities(ColorsList:dynamic,CitiesList:dynamic) {

 

let Catchall="__SelectAll__";

 

SalesFact

 

| where not(Catchall in (ColorsList) and Catchall in(CitiesList))

 

| lookup kind=inner

 

(Customers | where CityName in(CitiesList) or Catchall in(CitiesList))

 

on CustomerKey

 

| lookup kind=inner

 

(Products | where ColorName in (ColorsList) or Catchall in(ColorsList))

 

on ProductKey

 

}

 

 

 

The function applies a filter on the main table that will return 0 rows if both lists include the special value “__SelectAll__".

 

At this point, the query will apply the lookups but will terminate immediately and will not use any resources.

 

Each one of the joined table is filtered by the list of values and the special value returns all values.

 

You can see the function in the help cluster.

 

In Power BI

 

 

We will navigate to the function and provide the special value “__SelectAll__" as default values for both parameters:

 

 

 

 

 

 

 

 

 

 

 

 

We create two parameters two replace the default values in the step that invokes the function

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We use the Customers table and the Products table to create lists of cities and of colors by removing all other columns and removing duplicate rows.

 

It is recommended to use these tables in Dual mode.

 

Each column in these two tables is bound of one of the two parameters.

 

 

 

 

 

 

We need to allow multiple selection and allow selecting all values.

 

The default special value representing all values is the same as the default value if no selection is done.

 

Final report

 

 

 

 

Any kind of visuals can use the data returned by the function.

 

A measure is created to notify the user that a selection is needed.

 

Empty = if(countrows(FilterColorsCities)=0,"No selection, please select either cities or colors","")

 

 

 

A button is added to the page that will apply all filters after a selection.

 

 

 

 

 

 

Summary

 

 

 

 

Using KQL functions in conjunction with M dynamic parameters allow more control on the order of operations in the query and in some cases can block runaway queries that can drain resources and affect other users.

 

Continue reading...