This approach uses Fabric's Real-Time Analytics capabilities to efficiently query and aggregate the raw log data.
The Concept
The Capacity Metrics app is simply a Power BI report connected to a dataset. That dataset is populated by logs that record every activity in your Fabric tenant. By using a KQL Queryset, you can directly query these source logs, create the exact daily summary you need, and then load that small, pre-aggregated result into a new Power BI dataset.
Step-by-Step Guide
Create a KQL Queryset:
In your Fabric workspace, select the Data Engineer or Real-Time Analytics persona.
Create a new KQL Queryset. This is a code editor where you will write your query.
Query the Fabric Logs:
A KQL Queryset can query across databases. In the explorer pane, you'll need to find the database containing the Fabric logs. This is typically available as a read-only source.
Write a KQL query to aggregate the data. KQL is a language purpose-built for slicing and dicing log and time-series data with incredible speed.
Example KQL Query
Here is a sample query you can adapt. This query finds all interactive CU usage for the past 7 days and summarizes it by user and day.
// This query directly accesses the Fabric log data for CU consumption.
// It is significantly more performant than using DAX on the metrics app dataset.
// Replace 'FabricMetriosLog' with the actual name of your tenant's log database if different.
database('FabricMetricsLog').CapacityMetrics
| where TimeGenerated >= ago(7d) // Filter to a specific time range for efficiency.
| where OperationName has "Interactive" // Focus only on interactive operations (e.g., Power BI report loads).
| where isnotempty(UserPrincipalName) // Ensure we only count operations attributed to a user.
| summarize
// Sum the consumption, converting it to a double for accuracy.
TotalCU_Seconds = sum(todouble(TotalCUSeconds)),
// Count the distinct operations for context.
OperationCount = count()
// Group by the user's UPN and the day. The bin() function rounds the timestamp down to the start of the day.
by UserPrincipalName, bin(TimeGenerated, 1d)
| order by UserPrincipalName asc, TimeGenerated desc
If this explanation and solution resolve your issue, please like and accept the solution.
