Blog Post

Azure Data Explorer Blog
2 MIN READ

How Date and Time values from ADX are reflected in Power BI

DanyHoter's avatar
DanyHoter
Icon for Microsoft rankMicrosoft
Aug 27, 2023
    Summary Data in ADX (aka Kusto aka RTA in Fabric) almost always has columns that contain datetime values like 2023-08-01 16:45 and sometimes timespan values like 2 hours or 36 minutes. In...
Published Aug 27, 2023
Version 1.0
ADX
date
Fabric
kusto
PBI
power bi
RTA
Time
ItsBhatti's avatar
ItsBhatti
Brass Contributor
Aug 27, 2023

In Power BI, you can work with Date and Time values from ADX (Azure Data Explorer) by following these steps:

  1. Import Data: Connect Power BI to your ADX data source by selecting the appropriate connector. This might involve setting up the necessary connection details like server name, database, and credentials.

  2. Query Editor: Once connected, you'll enter the Query Editor where you can transform and shape your data before loading it into Power BI.

  3. Data Types: ADX usually provides Date and Time values in a specific format. Power BI will automatically detect these as text values. You need to change their data type to "Date/Time" for proper manipulation.

    • Select the column containing Date or Time values.
    • In the "Modeling" tab, find the "Data Type" dropdown and select "Date/Time."

  4. Date Hierarchy: If your Date column has a hierarchical structure (Year, Quarter, Month, etc.), you can create a Date Hierarchy to easily navigate time-based data.

    • Right-click on the Date column.
    • Choose "New Hierarchy" and then define the hierarchy levels.

  5. Time Intelligence Functions: Power BI has built-in time intelligence functions that allow you to perform calculations like year-to-date, quarter-to-date, etc.

    • Create new measures by going to the "Modeling" tab > "New Measure".
    • You can use functions like TOTALYTD, TOTALQTD, TOTALMTD, etc., to calculate time-based aggregates.

  6. Visualizations: Once your data is ready, you can create visualizations to represent Date and Time data.

    • Drag and drop a Date field to the "Axis" or "Legend" area of a visualization.
    • Depending on the visualization type, you can represent trends over time.

  7. Filters: You can use date filters to control the time range displayed in your visuals.

    • Add a date filter to a visualization by selecting the Date field, then choosing the "Filter" icon in the Visualizations pane.

  8. Custom Calculations: If you need to perform custom calculations involving Date and Time fields, you can use DAX (Data Analysis Expressions) formulas.

    • Create a new measure and use DAX functions to manipulate the date/time data.

Remember that the specifics might vary based on your data structure, the version of Power BI you're using, and the nature of your analysis. If you encounter any specific issues or need help with particular calculations, feel free to provide more details for further assistance.