Filtering multiple tables from Kusto by one time slicer in Power BI

Published Feb 27 2022 04:32 AM 1,875 Views
Microsoft

Using ADX Datetime columns in Power BI – part2

Using a time table to filter multiple Kusto tables with a single slicer

 

In the first part of the series I described some of the available features to support DateTime columns.

In this part I want to describe a method to filter multiple tables based on one DateTime  slicer/filter.

This is something that comes very naturally when you use date columns.

You create a calendar table and you create relationships between the calendar table and some other tables

We create a time calendar in the same way.

 

Creating a time calendar

 

The equivalent to the calendar table is a table with all the time elements in the period you want to analyze.

The problem with such a table is that there are theoretically an infinite number of such time elements.

The question is at what granularity do you want to slice time. The granularity can vary between hours and ticks.

If you go too granular, the table will be huge and if you use hours, you will not be able to slice by minutes.

I’ll use minutes in my example, but you can apply at any granularity, just remember that there are 2.6 million seconds in a month :smiling_face_with_smiling_eyes:

Like a date calendar, you can create a time table in M or in DAX. I prefer the M option.

First add two new M parameters

  •  LookBack is a number that will specify the number of days you want your table to cover going backward from today.
  •  GranularityMinutes is the number of minutes in a time bucket. If you specify 1, you’ll get a row for every minute and if you specify 10, a row for every 10 minutes

Create a blank query and paste this script in the advanced editor

 

let

    Today=Date.From(DateTime.LocalNow()),

    StartDate=Date.AddDays(Today,-LookBack),  TimeSeries=List.DateTimes(DateTime.From(StartDate),(LookBack+7)*24*60/GranularityMinutes,#duration(0,0,GranularityMinutes,0)),

    #"Converted to Table" = Table.FromList(TimeSeries, Splitter.SplitByNothing(), {"DateTime"})

in

    #"Converted to Table"

The main part is the List.DateTimes function that returns a list of DateTimes starting from a point in time.

I set the second argument to the number of time periods in the number of days + 7 so I don’t need to refresh the table every day.

The third argument specifies the granularity in minutes as a duration value.

Creating matching columns in the fact tables

 

Our goal is to create relationships between the time table and the fact tables that contain a DateTime column. In order to create the relationships, we need to add a column in the fact table that will match the values in the time table.

In the attached example I only have one fact table but you can repeat the same steps for every fact table.

We need a value that is the original DateTime column rounded to a minute or 5 minutes or 10 minutes according to the granularity of the time table.

We can do that using M in the or in the original table/function in Kusto.

In the M script we can add a new step:

= Value.NativeQuery( RawSysLogs1 ,"| extend TimeValue=floor(timestamp," &Text.From(GranularityMinutes) & "m),App=tostring(tags.appname)")

 

This step uses the Value.NativeQuery function to add a snippet of KQL that creates a column with the right granularity based on the M parameter GranularityMinutes.

At the same time I add another column extracted from the dynamic column tags.

I created a relationship between the new column TimeValue column in the fact table and the time table

 

Filtering using the time table

 

We can use all the types of filters possible for DateTime columns using the column DateTime in the time table.

As the table in the example is constantly streaming new values, we can use the relative time slicer.

We can look for rows ingested in the last minute or in the last 96 hours and see the numbers of rows for each application

 

Co-Authors
Version history
Last update:
‎Feb 27 2022 06:02 AM
Updated by: