Analysis Services Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, server health, objects, and server state, including database schema, active sessions, connections, commands, and jobs that are executing on the server.

Similarly, the DAX query view allows to return the information about any Power BI model objects such as data sources, tables, relationships, measures, partitions, KPI, columns, hierarchies, Roles, and calculated columns with their DAX descriptions from the Power BI model that is helpful while documenting the Power BI model. Earlier DAX studio software was needed to connect the Power BI model, or the Analysis server was to be connected to the SQL server to write the DMVs and get all the model-related details that needed additional effort.

There are over 50 new INFO DAX functions available now in Power BI. The existing TMSCHEMA DMVs are now available as DAX functions.

Benefits of DAX Query View

1. No need to use any other software to get details on Power BI Report
2. No longer do you have to use a different query syntax than DAX to see information about your semantic model. They are native DAX functions and show in IntelliSense when typing ‘INFO’.
3. Ability to join them together using other DAX functions. As a DAX function, the output is a Table data type, and existing DAX functions that join tables or summarize tables can be used.

Below is the list of DAX INFO functions that can be used as DMVs

If the DAX query view is not available on left side, enable DAX Query view from settings. To enable DAX Query view go to file -> options and settings -> options -> under preview features check on DAX Query View as shown below:

In Power BI Desktop I go to DAX query view and type in EVALUATE followed by INFO to see them all listed. I can continue typing or scroll down to INFO.MEASURES. Then I click run.

As shown in the above screenshot, INFO.Measures give all the measure details such as Measure Name, DAX that is used for the measures, format, shows if measures are hidden, and display folder(under what table or folder the measure depends).

Now the details of each object from the Power BI model can be added as the summary sheet of the Power BI. Documenting the model just got a lot easier. Copy the results with the Copy button.

For example, I can go to Model view and choose Enter data to Paste them into the model.

Then the starting summary sheet can be created that describes the Power BI Model by just creating a table and adding the required columns

Another combination that may be useful for the new INFO DAX functions is to explore the columns in your model.

DEFINE

    VAR _tables =

        SELECTCOLUMNS(

            FILTER(

                INFO.TABLES(),

                // Exclude hidden tables

                [IsHidden] = FALSE()),

            “TableID”,[ID],

            “TableName”,[Name] )

    VAR _columns =

        FILTER(

            INFO.COLUMNS(),

            // Exclude RowNumber columns

            [Type] <> 3 )

      VAR _result =

        SELECTCOLUMNS(

            NATURALINNERJOIN(

                _columns,

                _tables),

            “Table”,[TableName],

            “Column”,[ExplicitName],

            “Description”,[Description],

            “Column in Data Source”,[SourceColumn],

            “Data Category”, [DataCategory],

            “Column Type”,

                SWITCH(

                    [Type],

                    1,”Data column”,

                    2, “Calculated column”,

                    [Type] ),

            “DAX formula”, [Expression] )

EVALUATE

    _result

This query results in the Table name and the respective columns, column type defines if the column is calculated column with its DAX formula, this query also results if the source column name is different from the column name that is renamed to something else in Power BI report.

For the above query add the below query to see the just calculated columns

EVALUATE

    {COUNTROWS(

        FILTER(

            _result,

            [Column Type] = “Calculated column”))}

   EVALUATE

    FILTER(

        _result,

        [Column Type] = “Calculated column”)

Similarly, DAX queries using INFO can be used for multiple cases.

Note: These INFO functions will show in the DAX formula bar IntelliSense but cannot be used in calculated tables, calculated columns, and measures, they will throw an error.

Priyanka P Amte