Daily DAX : Day 134 INFO.PARTITIONSTORAGES

 In Power BI, the DAX function INFO.PARTITIONSTORAGES is one of the newer "INFO" functions introduced to provide metadata about a data model. Specifically, INFO.PARTITIONSTORAGES retrieves information about the storage details of partitions within the model. It’s part of a family of functions that mirror the Dynamic Management Views (DMVs) traditionally used in tools like SQL Server Analysis Services, but now made accessible directly within DAX for Power BI.

What It Does

INFO.PARTITIONSTORAGES returns a table containing details about how partitions are stored in the data model. This includes metadata such as partition names, sizes, row counts, and other storage-related attributes. It’s designed to give you insight into the underlying structure and performance characteristics of your data model’s partitions.

The function doesn’t take any arguments—it simply evaluates the current model and returns the relevant partition storage information. You’d typically use it in a DAX query with the EVALUATE statement, like this:

dax

EVALUATE

INFO.PARTITIONSTORAGES()

The output table might include columns like:

    Partition Name: The identifier of each partition.

    Table ID: Links to the table the partition belongs to.

    Storage Size: The size of the partition in memory or on disk.

    Row Count: The number of rows stored in the partition.

    Last Refreshed: When the partition was last updated.

(Note: The exact columns returned can vary slightly depending on the Power BI version and model specifics, as this function is still relatively new and evolving.)

Main Use Case

The primary use case for INFO.PARTITIONSTORAGES is model optimization and troubleshooting. It’s a tool for advanced users—think data modelers or BI developers—who need to analyze and fine-tune the performance of large or complex datasets in Power BI.

Here’s why it’s useful:

    Performance Tuning: By examining partition sizes and row counts, you can identify inefficient partitions that might be bloating your model or slowing down refreshes. For example, if one partition has a disproportionately large storage size, you might decide to split it into smaller, more manageable partitions.

    Resource Management: It helps you understand how your data is distributed across partitions, which is critical when working with incremental refresh policies or DirectQuery modes. You can ensure resources (like memory or processing power) are being used efficiently.

    Debugging: If a refresh fails or a report is sluggish, this function can help pinpoint whether the issue lies in a specific partition’s storage configuration.

Practical Example

Imagine you’re managing a Power BI model with a sales table spanning 10 years of data, split into yearly partitions. You notice refresh times are creeping up. Running INFO.PARTITIONSTORAGES reveals that the partition for 2023 has ballooned to 10 GB and 50 million rows, while older years are much smaller. This insight might prompt you to refine your partitioning strategy—say, breaking 2023 into quarterly partitions—to balance the load and speed up processing.

Limitations

    Not for Calculations: Unlike typical DAX functions (e.g., SUM or CALCULATE), INFO.PARTITIONSTORAGES can’t be used in measures or calculated columns—it’s strictly for querying metadata in DAX Query View or tools like DAX Studio.

    Advanced Audience: It’s not something casual Power BI users would touch; it’s aimed at those comfortable with model architecture.

    Context Dependency: The results reflect the current state of the model, so they’ll change after refreshes or structural updates.

In short, INFO.PARTITIONSTORAGES is a diagnostic tool that shines when you need to peek under the hood of your Power BI model’s storage. Its main value lies in helping you optimize performance and manage large-scale data efficiently—perfect for keeping complex reports running smoothly as your data grows.