minute read

DAX Info Functions: Saving Colleagues (and Myself) from Documentation Doom

Cristian Prifti

2 Oct

2024

Introduction

Tired of drowning in documentation? So am I! In this post, I’ll share some nifty tricks using DAX Info Functions to help "lazy (um, I mean efficient)" developers automate their Power BI documentation process. Because let’s face it, we all have better things to do—like refilling our coffee.

Within the Power BI ecosystem, there are at least 2 good external tools to support this process (Document Modeler and Power BI Helper). Both tools work very well, but they have an important prerequisite, as they must be accepted by the IT Organization.

In today's blog post we will look at how to leverage the newly published DAX Info functions, that will help us document our model. Those functions are using the already existing TMSCHEMA DMVs. While there are over 50 new Info functions, we will be focusing on 5 functions.

‍

NOTE
You should go first and read our previous blog post around the DAX Query View.

DAX Info Functions

To get started working with these functions, we will head over to the DAX Query View window, as all functions already have IntelliSense support and cannot be used currently as an expression of a calculated table.

‍

Compared to the DMVs, an advantage of using the Info functions is that we can merge multiple tables for a consolidated output. This will be done by leveraging the NATURALLEFTOUTERJOIN function.

For today's blog post we will use 5 different Info functions:

INFO.RELATIONSHIPS
INFO.TABLES
INFO.COLUMNS
INFO.PARTITIONS - We will use this one to get Power Query table definitions.
INFO.MEASURES

Let's start by looking at an example that gets the measure details.

Image showcasing the DAX Query View window with a sample code extracting DAX measure metadata. — DAX Query View Sample Measure Metadata Output

‍

All "Info" tables will have a large number of columns, so we use the SELECTCOLUMNS function on the base table that we will be working with to reduce the number of columns we will be working with.

Let's look now at the sample output of the base table:

Image showcasing the sample output of the base Measure Info table. — DAX Query View Measure Metadata Output

‍

To make it simpler to use, and to be able to leverage queries individually, we need to get the Info.Tables details as well for the table name, this then merged with the support of the NATURALLEFTOUTERJOIN.

This then gets us the final output:

Image showcasing the sample output of the query output for the measure metadata after the join with the tables metadata bringing in the table name which holds the measures. — DAX Query View Measure Metadata Output after Tables Join

‍

NOTE
You will find that some of the columns a bit too technical. In the upcoming feature, the Microsoft documentation will also describe all those elements.

‍

You can find below the codes for each category:

Table Details

--------------------------------Get Tables & Definitions-----------------------------------
EVALUATE

  var _GetTables =  
  SELECTCOLUMNS(
      INFO.TABLES(),    
      "TableID",[ID],    
      "Name",[Name],    
      "IsHidden",[IsHidden],    
      "ExcludeFromModelRefresh",[ExcludeFromModelRefresh])

   var _GetTableDefinitions = 
   SELECTCOLUMNS(    
       INFO.PARTITIONS(),    
       "TableID",[TableID],    
       "QueryDefinition",[QueryDefinition],    
       "State",[State],    
       "Type",[Type])

   var _TableMetadataOutput = NATURALLEFTOUTERJOIN(_GetTables,_GetTableDefinitions)
 
 RETURN
 	_TableMetadataOutput

‍

Measure Details

--------------------------------Get Measures & Definitions-----------------------------------
EVALUATE

var _GetMeasures = 
  SELECTCOLUMNS(
      INFO.MEASURES(),
      "TableID",[TableID],
      "Name",[Name],
      "DisplayFolder",[DisplayFolder],
      "Description",[Description],
      "Expressions",[Expression],
      "IsHidden",[IsHidden],
      "IsSimpleMeasure",[IsSimpleMeasure])

var _GetTables = 
  SELECTCOLUMNS(
      INFO.TABLES(),
      "TableID",[ID],
      "TableName",[Name])

var _MeasuresMetadataOutput = NATURALLEFTOUTERJOIN(_GetMeasures,_GetTables)

RETURN
_MeasuresMetadataOutput

‍

Relationship Details

--------------------------------Get Relationships-----------------------------------
EVALUATE
var _RelationshipsMain =
SELECTCOLUMNS(
    INFO.RELATIONSHIPS(),
    "FromTableID", [FromTableID],
    "FromColumnID",[FromColumnID],
    "ToTableID",[ToTableID],
    "ToColumnID",[ToColumnID],
    "IsActive",[IsActive],
    "FromCardinality",[FromCardinality],
    "ToCardinality",[ToCardinality],
    "SecurityFilterFlow",[SecurityFilteringBehavior],
    "CrossFilterBehavior",[CrossFilteringBehavior]
)

var _FromTable =
SELECTCOLUMNS(
    INFO.TABLES(),
    "FromTableID",[ID],
    "FromTableName",[Name]
)

var _ToTable =
SELECTCOLUMNS(
    INFO.TABLES(),
    "ToTableID",[ID],
    "ToTableName",[Name]
)

var _FromTableJoin =
NATURALLEFTOUTERJOIN(_RelationshipsMain,_FromTable)

var _ToTableJoin =
NATURALLEFTOUTERJOIN(_FromTableJoin,_ToTable)

var _FromColumn = 
SELECTCOLUMNS(
    INFO.COLUMNS(),
    "FromColumnID",[ID],
    "FromColumnName",[ExplicitName]
)

var _ToColumn = 
SELECTCOLUMNS(
    INFO.COLUMNS(),
    "ToColumnID",[ID],
    "ToColumnName",[ExplicitName]
)

var _FromColumnJoin =
NATURALLEFTOUTERJOIN(_ToTableJoin,_FromColumn)

var _ToColumnJoin =
NATURALLEFTOUTERJOIN(_FromColumnJoin,_ToColumn)

RETURN
_ToColumnJoin

‍

Column Details

--------------------------------Get Columns-----------------------------------
EVALUATE
var _GetColumns= 
SELECTCOLUMNS(
    INFO.COLUMNS(),
    "TableID",[TableID],
    "Name",[ExplicitName],
    "Description",[Description],
    "ExplicitDataType",[ExplicitDataType],
    "DataCategory",[DataCategory],
    "IsHidden",[IsHidden],
    "Unique",[IsUnique],
    "IsKey",[IsKey],
    "Isnullable",[IsNullable],
    "SummarizeBy",[SummarizeBy],
    "Type",[Type],
    "AttributeHierarchyID",[AttributeHierarchyID],
    "Expression",[Expression]
)
var _GetTables = 
 SELECTCOLUMNS(
    INFO.TABLES(),
    "TableID",[ID],
    "Name",[Name],
    "IsHidden",[IsHidden],
    "ExcludeFromModelRefresh",[ExcludeFromModelRefresh]
)

var _TableMetadataOutput = 
NATURALLEFTOUTERJOIN(_GetColumns,_GetTables)

RETURN
_TableMetadataOutput

‍

NOTE
You could also have a second SELECTCOLUMNS function at the end of the query, if you would like to reorder the columns after the joins.

‍

If you are just testing things out, you can also run all codes in the same DAX Query pane, and we will get 4 different outputs that we can look at. Any of those outputs can also be copied directly from the output.

Image displaying the DAX query output when multiple queries reside in the same Query Pane. — DAX Query Output with Multiple Evaluates

‍

You can also save those queries in the Power BI Desktop file, to make it easier to make changes in the future.

Running the Queries

The easiest way to achieve this is with the support of Power Automate and running the queries against the dataset. You can read more of this in one of our older blog posts.

We will discuss using a predefined template, though there is another option to have everything centralized in a single file, but this requires access to the Graph API. If individual file can be an option, this becomes more easier, as it only requires generating a CSV file (we have a predefined action for this in Power Automate) and then save the file.

Template File

Consolidated Template File

This solution starts from a template excel file, containing all outputs. In this case we will use a single template file that will contain all the outputs.

This follows a similar approach in terms of actions, but instead of creating a new file for each section, we will copy the template and fill in all the required details in the respective table.

Image displaying the sample Power Automate flow for extracting relationships metadata and saving it in an Excel file. — Power Automate Sample Flow for Relationships Metadata

‍

Now let's also understand everything:

GetRelationships (Action: Run a query against a dataset) - This runs the query against the semantic model.

Image displaying the Power Automate action that runs the DAX Info Function and Exporting Relationship Metadata from the Semantic Model. — DAX Info Function Exporting Relationship Metadata from the Semantic Model.

‍

NOTE
In this example we have a manual input with a manual trigger of the Power Automate flow. However, the input fields can also be provided in a Power Platform solution through variables like you will see below when we get the template file content. You can check this blog post talking about the data extraction.

‍

ParseRelationships (Action: Parse JSON) - This action will parse the output received from Power BI.

Schema output can always be generated from a sample while using a historical run to get the output.

‍

NOTE
You can read more about this action in this blog post.

‍

GetTemplateContent (Action: Get file content using path) - This gets the content of the template file, including the template tables, as we need to work with Excel tables, so that we can add data inside.

Image showcasing the Get File Content by using path action. — Step that will get the template file content

‍

CreateMetadataFile (Action: SharePoint Create file) - This will create the new file that will be filled with our metadata export.

Image showcasing the Step that will use the template content from the previous step to create our new output file. — Step that will use the template content from the previous step to create our new output file

‍

‍Wait (Action: Delay) - This step is more of a recommendation, from my own experience, as we can have a scenario where the file visibility has a certain latency, leading to a failure in the next step.

Image showcasing the action meant to just add a delay until the new file is visible online.

‍

GetOutputTables (Action: Get tables) - This action will read all the Excel tables present in the file that was just created.

Image showcasing the action extracting all tables present in the output. — This action extract all tables present in the output

‍

FilterRelationshipsRow (Action: Filter array) - This will filter the previous step for the table in which we will need to add data (in this case this is the relationships metadata). From this point onwards the actions will repeat for each metadata table.

Image showcasing the filter array action, that will extract the technical table ID, which will be used to append rows.

‍

GetRelationshipsID (Action: Compose) - This action will extract the actual ID from the array we just filtered.

Image showcasing the compose step, that will extract from the filter array action the single ID out. — You can see the full function below

Function used:

first(body('FilterRelationshipsRow'))?['id']

‍‍

AddRelationships (Action: Each + Add a row into a table) - This is the final step, that adds the actual metadata in the respective table.

Image showcasing the final step, that will add the metadata rows into the corresponding table. — The Each step will be added automatically when you will select the first dynamic field.

‍

This is how a sample output would look like:

Image showcasing the sample output table from Microsoft Excel.

‍

In conclusion, let’s be honest—no one wants to do documentation, but we’ve got to look out for ourselves (and our fellow developers). Using DAX Info Functions is like giving yourself a shortcut when also combined with an automatic flow.

‍

Share this post

Power BI