Daily DAX : Day 70 INFO.TABLES
INFO.TABLES: This function returns a table that provides metadata about all the tables within your Power BI model. It mirrors the TMSCHEMA_TABLES DMV, which provides information like table names, descriptions, whether the table is hidden, and more.
Use Case:
Scenario: Model Metadata Analysis
You're a data modeler or BI developer tasked with auditing your Power BI model structure before deployment or during maintenance. Here's how INFO.TABLES could be used:
Audit Table Structure:
You can use INFO.TABLES to get a comprehensive list of all tables in your model, including their properties like visibility, description, and type.
Example DAX Query:
dax
EVALUATE
INFO.TABLES()
This will return a table with columns like:
TableID: A unique identifier for the table.
TableName: The name of the table as it appears in Power BI.
Description: Any description associated with the table.
IsHidden: A boolean indicating if the table is hidden from the user interface.
TableType: The type of the table (e.g., Calculated or Physical).
Model Documentation:
You can generate documentation by querying INFO.TABLES to list all tables in your model, which can be particularly useful for handing over the model to another team or for compliance and governance purposes.
Example Use in Power Query or DAX Query View:
You could write a measure or query to export this information:
dax
Model Tables Overview =
VAR TablesInfo = INFO.TABLES()
RETURN
SUMMARIZE(
TablesInfo,
[TableName],
"Is Hidden", [IsHidden],
"Description", [Description]
)
This creates a summarized view of your tables, possibly for use in a table visual in Power BI.
Quality Checks and Optimization:
Use this function to check for unnecessary tables, to review if any tables should be hidden, or to ensure all tables have proper descriptions for better model management.
Example to Filter Hidden Tables:
dax
HiddenTables =
FILTER(
INFO.TABLES(),
[IsHidden] = TRUE
)
This measure would allow you to see all hidden tables, helping you manage or review why they are hidden or if they should be visible.
Conclusion:
INFO.TABLES is particularly useful for anyone managing or auditing Power BI models, providing a programmatic way to access and manipulate table metadata without manual inspection. This can lead to more efficient model management, better documentation, and enhanced model optimization processes. Remember, the exact output and functionality might depend on the version of Power BI Desktop you're using, as these new functions might still be in preview or not fully documented.
Comments
Post a Comment