Introduction

 

M  dynamic parameters are a very important tool when working with Power BI and Kusto/RTA data .

You can read about it here and also in my article here.

When you select a value in a column that is bound to a parameter , there are many limitations on the way you can filter the value as explained here.

I recently received a request from a customer that needed to work around many of these limitations.

I came up with a solution that require some extra steps but I hope it is worth it.

 

Customer scenario

The customer is a large manufacturing company.

It has many plants with many lines in each and machines in each line.

A batch goes through a process and moves between lines and machines in a plant.

 At each stage telemetry is collected and stored in an Azure Data Explorer (ADX) database.

The PBO report need to present a hierarchy of plant/line/machine/batch-id and start and end timestamps when the batch was processed.

The selection of batch-id and start/end timestamps need to be fed to a KQL function that analyze the telemetry.

The customer tried to bind three columns in the same table to parameters and it didn’t work because only one column in a table can be bound.

Simulated scenario

I created a similar scenario to demonstrate the solution.

Based on the ContoSales database, I created a table with a single product and a date in each row.

These two columns need to go feed a function that will filter the Sales table for the product and 7 days starting from the date.

Selecting the product-date combination needs to be dםne with a hierarchy slicer or even from a table which of course does not work with M parameters,

 

Step by step

1.     Concatenated parameter

The critical idea was suggested by my colleague Gilles LHerault .

He suggested having the function receive a single parameter which will actually contains three concatenated values with a separator between the values. This can work with any number of parameters. In the attached PBIX I use just two values.

2.     The function

.create-or-alter function  ProductInaWeek(Key:string) {

let KeyDynamic=split(Key,"~");

let Day=todatetime(KeyDynamic[0]);

let Product = tolong(KeyDynamic[1]);

SalesFact

| lookup kind=inner Products on ProductKey

| where DateKey between(Day..7d) and ProductKey ==Product

| project DateKey,ProductName,ProductKey,SalesAmount

}

 

The split function breaks the parameter into a dynamic array and the two parts ate extracted from the dynamic column and used in the where clause.

The function uses lookup to bring more columns from the product table to allow for a hierarchy slicer.

3.     Selection table

The table WeekSale contains the product and date pairs used for picking the right parameter combination.

A Key column is created concatenating the product key and date in ine string with the ~ separator.

A second table named WeekSale – twin is created referencing the WeekSale table.

A relation is created between the two tables based on the Key column.

The relation is 1:M with a single filter direction  WeekSale -> WeekSales – twin

The Key column is bound to the parameter.

4.     Selecting the values

The goal is to allow a friendly selection not showing the concatenated parameter but rather each parameter separately.

The actual selection is done on the WeekSale table using the hierarchy for selecting a product and a date.

There are no restrictions on the filters because no M parameters are bound to any column.

 

5.     Using ChicletSlicer

Once a single row is selected the twin table is filtered via the relationship.

A copy is needed to be able to select a value without limitations.

A custom visual called ChucletSlicer  is used to apply the M parameter value selection.

 

 

 

 

The chiclet can be made almost invisible but not hidden.

The Key column from the twin table is used in the Chiclet slicer.

One of the unique properties of the Chiclet is force selection:

 

 

When this setting is on, the visual simulates user selection when only one value is selected.

So, when the actual slicer or even a table selects one row, the Chiclet also has one row selected and it simulates user selection which forces the Key value into the parameter and the query to be executed.

Summary

In order to overcome some of the limitations of M dynamic parameters, two tables are used + a function that receives all its parameters in one string + a Chiclet slicer custom visual.

It is not very straightforward but is does the job 😊