In this article, I am going to share my recent experience in building Real-Time Dashboards on Fabric. You will learn how to allow dashboard users to select their local time zone using a dashboard parameter.
It’s been a while since I last wrote a technical blog post here on DataChant. I’ve been meaning to return and share my learnings and insights, but since June last year, most of my time is focused on BI Pixie (You can find out here all the new capabilities we have added). I recently realized that the only way I can share knowledge in my current capacity is close to real-time, without preparation, as I discover new things or solve problems that are worth sharing. I am sorry if it’s going to be a bit random for you ????
Why Real-Time Matters
If you’ve built Power BI solutions, you know how valuable it is to capture the full picture of user adoption, performance, and governance. But most dashboards are still snapshots—updated daily, hourly, or on-demand. What if you could go beyond historical analysis of trends and capture what is most important to you in real-time? With Fabric Real-Time Intelligence (RTI), you have this opportunity.
BI Pixie Integration with Fabric Real-Time Intelligence (RTI)
As BI Pixie has been expanded from measuring the effectiveness of your reports to monitoring data exports and flaws in Row-Level Security (RLS), there is a stronger need to support real-time analytics for governance and security use-cases. So, this month, I started working with Fabric Real-Time Intelligence (RTI) to support real-time detection of use-cases new capabilities in BI Pixie (You can find Fabric RTI Dashboard in the flow diagram below as the latest piece of the solution).
With the new integration that I developed this month with Fabric RTI, organizations can deploy BI Pixie on their Azure account, and add Fabric RTI Eventstream and RTI Eventhouse to analyze usage events in real-time.
After the Eventstream and Eventhouse were ready, it was time to build a Real-Time Dashboard.
Building tiles in Fabric RTI dashboards requires some knowledge of Kusto. While I am not an expert in Kusto, the wonders of AI made the learning a fun experience and I can now implement the most advanced business logic that I need in Kusto, instead of resorting to super-complex and slow queries using M code in Power Query (Side note: I can share more in the future how to expand from Power Query to Kusto).
Real-Time Dashboards Parameters
As I started adding more tiles to my v1 dashboard, I discovered the wonders of parameters in RTI dashboards. Real-Time Dashboard Parameters were introduced by Microsoft in November (Nov, 2024) and are used as building blocks for filters in the dashboards. They’re managed in the dashboard scope, and can be added to queries to filter the data presented by the underlying visual.
After you create a new parameter, you need to use it in a where clause in the queries of the Dashboard tiles. Otherwise, the filters will not work. To quickly learn how to use the parameter in your queries, click the Show sample link in the Edit parameter pane.
After you click Show sample, you can see a sample code that can guide you on how to use the parameter to filter the data in the tiles.
After I got the gist of using parameters, I thought it could be useful to offer users the ability to select their time zone and see data in local time.
The Timezone Challenge in Real-Time Dashboards
When you work with datetime columns in Kusto, you quickly realize that you need to pay closer attention to time zones. In Kusto, according to Microsoft here, a datetime value is always in the UTC time zone. If displaying datetime values in other time zones is required, you should use the function datetime_utc_to_local. This function cannot automatically detect your current time zone (e.g. the one you have in your computer or browser), and as a result, you need to explicitly enter the time zone as the second argument.
My data in BI Pixie is collected in UTC time. To make the dashboard user-friendly for global BI teams, I wanted to give users the ability to select their time zone and be able to view datetime columns in local time. Can we deliver the final experience as shown in this screenshot?
The motivation: Let a user in Sydney or London see timestamps in their local time context rather than trying to mentally convert from UTC. In the next part of this article, I will walk you through the solution.
Populating the Time Zones in a Parameter
In Kusto, converting UTC to local time is straightforward:
let dt = now(); datetime_utc_to_local(dt, "Eastern Standard Time")
My first task was to find out how to get the full list of time zone strings. For obvious reasons, I didn’t want to create a static list of all time zones. Luckily, in Kusto, you can generate the list of time zones using the datetime_list_timezones() function.
I created a new parameter, selected Query as the Source and then selected Edit query to define the dynamic list of time zones.
In Add a query, I entered the function datetime_list_timezones() and encountered the error below:
Semantic error: Top-level expressions must return tabular results. (Hint: If you're trying to output a scalar value, use the 'print' operator.)
We love riddles, especially when they come with a free hint like the one above. Taking the trial and error approach, I applied the print function as shown below:
print(datetime_list_timezones())
Since I didn’t get any error after I clicked Run on the new query, I thought I had made real progress. But only because I didn’t pay attention to the Results section. In the screenshot below, you can see that the Results table has only one cell that contains the entire list of time zones as a string. This will not get us anywhere. But let’s proceed and complete the setup of the new parameter.
Consistent with the single cell we saw earlier, you can now see that the new time zones parameter appears in the dashboard and offer only one value: All the time zones in a single string.
Then, how would we resolve this issue? Can we populate the time zones in the parameter?
“mv-expand” To The Rescue
The original datetime_list_timezones returns a dynamic array, that cannot be used as a table. To turn dynamic array into a table you can use mv-expand to flatten the time zones.
Here is the new expression that I used:
print timezones = datetime_list_timezones() | mv-expand timezone = timezones | project timezone
The first line returns a single cell table with all the time zones as one string. It uses timezones as the name of the column. The second line performs the mv-expand operation on timezones column and return it as a new column: timezone.
If you know Power Query, you can think about mv-expand as the List Expand function in Power Query. Expanding a column with list objects, splits the list in each cell into a row for each value in the list. Values in the other columns are duplicated in each new row. The 3rd step keeps the timezone as the only column in the table.
A Side Tip for Power Query Users Learning Kusto:
If you are not sure how a chunk of complex Kusto code works–especially when the code was generated by Copilot or other AI, think about the problem as you would solve it in Power Query. You know how to use Power Query Editor to select each step and inspect the transformed results in the preview pane. In Kusto, you can do the same by removing some sections of the code. The transformation steps in Kusto are the lines that are separated by pipes (|). You can run the first line, and see the results, then add the next line and run the query. Check the results to get a better understanding of that step. Keep adding the lines and learn how the results are getting built. You can even group a set of steps into a single table using the let command (Similar to references in Power Query).
Back to the time zones. The query above gave me the right output — one row per timezone, exactly what RTI needs. As you complete the setup of the new parameter, you will encounter the errors in the Query results section of the Edit parameter pane.
Don’t worry. These errors are probably a bug in RTI Dashboard user interface. Our Kusto query works just fine. You will need to keep scrolling down until the end of the list and select timezone (dynamic) as Value column.
For the first time, I was able to have a list of time zones in the parameter. But something is still off. The sort order of the time zones is alphabetic. It would be awesome in the future to have the ability to sort the list according to a second column in the results table.
Sorting the Time Zones by Offset
I decided to work around the current limitations and ended up with a solution that performs the following logic on top of the query above.
- In each row of the table of time zones, add a new column with the UTC current time using the
now()function (All values will have the same value), - Add a new column for the current local time in each time zone, based on the two columns using
datetime_utc_to_local. - Add a column for the time difference in minutes between the UTC and local time. This will be the offset that will be used to sort the time zones.
- Format the label of the time zone by combining between the offset and the original time zone value.
- Sort the table by the offset and then by the time zone string (This will ensure that cities within the same UTC offset will be sorted in alphabetical order.
- Keep the original time zone string and the new label.
Here’s the final query I ended up using:
print timezones = datetime_list_timezones()
| mv-expand timezone = timezones
| extend current_utc = now()
| extend local_time = datetime_utc_to_local(current_utc, timezone)
| extend utc_offset_minutes = datetime_diff("minute", local_time, current_utc)
| extend offset_hours = utc_offset_minutes / 60.0
| extend offset_hours_floor = round(offset_hours, 0)
| extend offset_hours_str = iif (
offset_hours_floor == offset_hours,
tostring(toint(offset_hours_floor)),
tostring(offset_hours))
| extend label = strcat("UTC", iif(offset_hours >= 0, "+", ""), offset_hours_str, ": ", timezone)
| sort by offset_hours asc, label asc
| project timezone, label
Now, comes another tricky part. We will need to maintain both the original time zone string and the time zone label we use in the parameter. Next, every tile we create will need to load the time zone label and find the original time zone string before we convert UTC to local time. To create a clean solution, I used Base queries (Learn here about base queries in Fabric RTI).
The first base query returns the table of timestamp and label using the query above. I named the base query timezones_base_query. It will be referenced by the parameter to load the sorted labels and by all the queries of the dashboard tiles.
Here is the query I used as the final time zone labels that populate the time zones parameter:
timezones_base_query | project label
Next, I created a second base query, named selected_timezone, to find the time zone from the label the users select in the time zones parameter. Here is the query I used:
timezones_base_query | where label == _timezone | project timezone
selected_timezone returns a single cell table with the time zone we will need to use to convert the UTC time to local time in any query we will use in tiles. The final logic that I added to the existing queries of the dashboard tiles is as follows:
<table name> | extend local_time = datetime_utc_to_local(timestamp, toscalar(selected_timezone)) | project-reorder local_time | project-away timestamp
In BI Pixie, the main table uses timestamp as the column that stores the UTC datetime. In the “| extend" line, we add a new column, local_time, with the local time using timestamp and the selected_timezone base query. But since it returns a (single cell) table and not a string, we need to convert the selected timezone from a table into scalar using toscalar function. The next steps are simple. We reroder the table and move the local_time column to be the first column in the tile, and then we remove the original timestamp column to avoid redundant datetimes.
Conclusions
In this article, I shared my recent learnings in building Real-Time Dashboards on Fabric. We learned a technique to allow users to select their local time zone from a user-friendly sorted list of time zone labels in a dashboard parameter. I shared the Kusto base queries you can use and how to modify your existing queries in tiles to show the local time based on the user’s selected time zone.
Did you find this article useful? Please share your thoughts in the comments below,
It is good to be back. Stay tuned for more updates. BI Pixie integration with Fabric Real-Time will go live soon. If you are interested in early detection of Row-Level Security (RLS) flaws across all your semantic models, please reach out. You can start a trial of BI Pixie in this form. I would love to meet for feedback and a live-demo.
