Power BI recently released a new report that allows us to monitor usage metrics in workspaces. This report is a great improvement compared to the previous version. However, it only contains usage data for the last 30 days. So, if you want to build historical usage metrics, you’ll need to create your own dataset.

In this post, we’ll use a Fabric capacity to build a history of usage metrics in Power BI, but we don’t need all the workspaces we want to monitor to be hosted on a Fabric capacity.

What usage metrics in Power BI covered?

Some of the metrics covered by the Usage Metrics report include:

• Report usage: provides info about report views and viewers, such as the number of users who viewed the report by date.
• Page usage: provides similar info but at the page level.
• Report performance: shows typical report opening times, broken down by consumption method and browser types.

One of the reasons I’m writing this post is because I was asked to provide the number of page views of specific reports. As far as I know, the usage metrics in Power BI are the only place where you can get this level of detail. Even scanning the tenant only provides activity at the report level but not the page level.

Why keep a history of usage metrics in Power BI?

Keeping a history of usage metrics in Power BI helps you understand how reports and pages are being used over time. It’s useful for spotting trends, knowing which content gets the most attention and which content is not being used, so you can clean up and remove the ones that are not needed. “Remove to Improve“.

Create the Usage Metrics semantic model

The first step requires some manual work, as we need to repeat the same process for each workspace where we want to track the history of usage metrics.

• Click on any report in your workspace
• Select View usage metrics
• This will generate a semantic model (formerly called dataset) called Usage Metrics Report.

Optional step:

• You can save a copy and download it to explore the data and write the DAX query you need.
• You can also connect directly to the model from Power BI Desktop, which is what I did below.

Create the DAX query

Once connected to the semantic model in Power BI Desktop, we will create a visual table to generate the base DAX query.

In my scenario I need only the following info:

• Date
• ReportName
• SectionName (this is the Page Name)
• UniqueUser (The user UPN)
• Page Views (total views summarized by day, page, user)

We can then use Performance Analyzer to capture the query and open it in DAX Studio or DAX Viewer to clean it up.
You can add a visual filter based on the date, as we’ll use this column later for incremental data loading.

The generated DAX query will contain functions we don’t need (e.g. ORDER BY, SUMMARIZED, ROLLING, TOP, etc.). Once cleaned up, my query looks like this (you can add more columns if needed):

EVALUATE
FILTER(
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            'Report pages'[SectionName],
            'Reports'[ReportName],
            'Users'[UniqueUser],
            'Dates'[Date],
            'Report page views'[WorkspaceId],
            FILTER(
                KEEPFILTERS(VALUES('Dates'[Date])),
                'Dates'[Date] > (DATE(2025, 3, 1) + TIME(0, 0, 1))
            )
        ),
        "Page_views", 'Model measures'[Page views]
    ),
    [Page_views] > 0
        && NOT(ISBLANK([Page_views]))
)

Your query might look slightly different than mine if you brought in more columns, but as long as you have a date filter, you’re good. We’ll use this query in a notebook to extract the data daily and load it incrementally.

Create the Lakehouse tables

Since we’ll extract the data via a notebook, we need two tables in our Lakehouse:

Timestamp Table

This stores the “last updated date” in other word, the last time we retrieved the usage metrics from each semantic model :

spark.sql("""
CREATE TABLE IF NOT EXISTS dbo.UsageMetricsLastUpdated (
    WorkspaceName STRING,
    DatasetName STRING,
    LastUpdatedDate DATE
) USING DELTA
""")

We will need to insert the name of each workspace and semantic model manually. This can be done like this:

spark.sql("""
INSERT INTO dbo.UsageMetricsLastUpdated
VALUES 
('MyWorkspaceName', 'MyDatasetName', DATE('2025-04-14'))
""");

Page Views Table

The Page Views table will store the historical page view data for all our specified workspaces

spark.sql("""
CREATE TABLE IF NOT EXISTS dbo.PageViews (
    reportName STRING,
    pageName STRING,
    user STRING,
    date DATE,
    workspaceID STRING,
    numberOfViews INT
) USING DELTA
""")

Create the Notebook

Once your tables are ready, create a notebook to retrieve the historical usage metrics for all Power BI workspaces for which we’ve generated a Usage Metrics dataset:

import requests
import pandas as pd
import sempy.fabric as fabric
import pyspark
import pandas as pd
from pyspark.sql.functions import col
from datetime import datetime
from datetime import date

# Today's date for watermark update
today_str = date.today().isoformat()
today_str

# Load the dataframe
df = spark.sql("SELECT WorkspaceName, DatasetName, LastUpdatedDate FROM dbo.UsageMetricsLastUpdated")
display(df)

rows = df.collect()

for row in rows:
    # Step 1: Convert date to DAX format
    current_date = row['LastUpdatedDate']
    dax_date = f"DATE({current_date.year}, {current_date.month}, {current_date.day})"
    
    # Step 2: Extract dataset and workspace names from current row
    dataset_name = row['DatasetName']
    workspace_name = row['WorkspaceName']
    
    # Step 3: Build the DAX query with parameters
    dax_query = f"""
    EVALUATE
    FILTER(
        ADDCOLUMNS(
            SUMMARIZECOLUMNS(
                'Report pages'[SectionName],
                'Reports'[ReportName],
                'Users'[UniqueUser],
                'Dates'[Date],
                'Report page views'[WorkspaceId],
                FILTER(
                    KEEPFILTERS(VALUES('Dates'[Date])),
                    'Dates'[Date] > {dax_date}
                )
            ),
            "Page_views", 'Model measures'[Page views]
        ),
        [Page_views] > 0
            && NOT(ISBLANK([Page_views]))
    )
    """
    
    # Step 4: Evaluate the DAX query against the target dataset and workspace
    try:
        df_dax_result = fabric.evaluate_dax(dataset_name, dax_query, workspace_name)
        
        # Step 5: Save result to the lakehouse table
        # Convert the result back to Spark DataFrame
        spark_df_dax_result = spark.createDataFrame(df_dax_result)

        # Rename the columns in the Spark DataFrame to match the Delta table schema
        spark_df_renamed = spark_df_dax_result.selectExpr(
        "`Reports[ReportName]` as reportName",
        "`Report pages[SectionName]` as pageName",
        "`Users[UniqueUser]` as user",
        "`Dates[Date]` as date",
        "`Report page views[WorkspaceId]` as workspaceID",  # Correct column name
        "`[Page_views]` as numberOfViews"
        )

        # Cast the `numberOfViews` column to IntegerType
        spark_df_renamed = spark_df_renamed.withColumn("numberOfViews", col("numberOfViews").cast("int"))
        # Write the DataFrame to the Delta table
        spark_df_renamed.write.format("delta").mode("append").saveAsTable("dbo.PageViews")
        # step6: Update watermark of the table UsageMetricsLastUpdated
        update_query = f"""
        UPDATE dbo.UsageMetricsLastUpdated
        SET LastUpdatedDate = DATE('{today_str}')
        WHERE WorkspaceName = '{workspace_name}' AND DatasetName = '{dataset_name}'
        """
        spark.sql(update_query)
        print(f"Successfully processed dataset: {dataset_name} in workspace: {workspace_name}")
    
    except Exception as e:
        print(f"Failed for dataset: {dataset_name} in workspace: {workspace_name}. Error: {e}")

Notebook breakdown

Here’s what each part does:

• Step 1: Convert the last update date to DAX format {YYYY, MM, DD}
• Step 2: Store dataset and workspace names in variables
• Step 3: Create the DAX query using the LastUpdatedDate
• Step 4: Run the query against the target semantic model using the function evaluate.dax
• Step 5: Insert the result into the PageViews table
• Step 6: Update the LastUpdatedDate for that model
• Repeat for each row in the UsageMetricsLastUpdated table

Once this runs, you’ll have a table “PageViews” ready to build a Power BI report on top of it and of course if we run the notebook every day it will keep the history of usage metrics in Power BI beyond the 30 days limit.

Refresh the notebook

The notebook will need to be scheduled to run on a daily basis or weekly basis, it will work at any frequency. Power BI only refreshes the Usage Metrics semantic model once per day, so running the notebook more frequently won’t bring any new data.

Side note

The new Usage Metrics is still in preview so pay attention to any changes, especially renaming of columns which might break the notebook., but debugging it should be simple though!

Kudo to Michael Kovalsky whom I met this year (2025) at Fabcon Vegas, I briefly explained what I was doing with my notebook and he reminded me that we can pass the workspace name as a parameter to the function evaluate.dax, this led me to redesigned the whole solution as well as rewriting this post. But trust me this newer version is way less cumbersome than my first attempt.

Conclusion

Apart from some initial manual setup to generate the semantic model, it’s pretty straightforward to keep history of usage metrics in Power BI beyond the 30-day limit and store the metrics into a single place.
Note that I designed this solution specifically to retrieve the Page Views if you don’t need the Page Views there’s other existing solution like scanning the Power BI tenant activities.

We could also enable OneLake data integration to automatically retrieve the metrics into One Lake but that only works on workspaces assigned to Fabric capacity. My solution works across all workspaces, even the one on Pro-License as long as the notebook runs in a workspace assigned to Fabric, this was also one of my constraint.