Daily DAX : Day 441 INFO.STORAGETABLECOLUMNS

INFO.STORAGETABLECOLUMNS DAX Function

The INFO.STORAGETABLECOLUMNS is a tabular DAX function in Power BI that returns metadata about the storage of columns in the semantic model's storage tables. It corresponds directly to the Dynamic Management View (DMV) DISCOVER_STORAGE_TABLE_COLUMNS used in Analysis Services.

Syntax

INFO.STORAGETABLECOLUMNS (
[ [, ] [, ...]])

Parameters are optional restrictions (filters) on columns like TableID, ColumnID, etc., to narrow down the results.

What It Returns

A table containing detailed information about how columns are stored in the Vertipaq engine (the in-memory storage engine for Power BI and Tabular models). Typical columns in the result include:

  • TableID and ColumnID
  • DictionarySize (size of the dictionary for value encoding)
  • DataSize (compressed data size)
  • RowsCount
  • BitsCount (bits used for encoding)
  • And other storage-specific metrics

Use Cases

This function is primarily used for model optimization and analysis:

  • Identifying which columns consume the most memory/space in the model.
  • Analyzing compression efficiency of columns (e.g., high dictionary size vs. data size).
  • Finding large or inefficiently stored columns to optimize (e.g., by splitting, removing, or changing data types).
  • Performance tuning of large semantic models by understanding storage footprint.

It is especially useful in scenarios where the Power BI file size is large, and you need to reduce it without losing critical data.

Example Usage

Run in Power BI's DAX Query View (recommended for testing):

EVALUATEINFO.STORAGETABLECOLUMNS()

To find the largest columns by data size:

EVALUATE
TOPN(
    20,
    INFO.STORAGETABLECOLUMNS(),
    [DataSize],
    DESC
)

Limitations

  • Cannot be used in calculated columns, measures, or calculated tables (like most INFO functions).
  • Best used in DAX Query View or external tools like DAX Studio.
  • Requires appropriate permissions (often admin-level on the model).
  • Some sources note it may be considered deprecated or undocumented, but it remains functional.

Note: For simpler model size analysis, tools like Power BI Helper or Vertipaq Analyzer in DAX Studio/Tabular Editor are often recommended as alternatives.

Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 446 INFO.CSDLMETADATA

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more