DAX query view introduces new INFO DAX functions

DAX query view, released in public preview last month, in the December 2023 Power BI Desktop release has exciting updates including the new INFO DAX functions and improvements to the Update model CodeLens.

There are over 50 new INFO DAX functions now available! Those of you who are familiar with the Dynamic Management Views, or DMVs, of models in Power BI, Azure Analysis Services, and SQL Server Analysis Services will recognize these as the TMSCHEMA DMVs. The existing TMSCHEMA DMVs are now available as DAX functions. Providing them as DAX functions gives the following benefits:

• 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 you type INFO.
• You can join them together using other DAX functions! Existing DMV query syntax does not allow you to join them. As DAX function, the output is a Table data type and existing DAX functions that join tables or summarize tables can be used.

Below is a full list of the new INFO DAX functions.

Here is more information about a few that may be most useful: 

• INFO.TABLES contains information about the tables in the model, such as the table name, description, and whether it is hidden or not. 
• INFO.COLUMNS contains information about the columns in a model, such as the column name, data type, and whether it is hidden or not. 
• INFO.MEASURES contains information about the measures in the model, such as the measure name, expression, and format string. 

Let’s look at INFO.MEASURES and demonstrate now they can be joined to other INFO functions in DAX query view.

To follow along, download the Store Sales PBIX from https://learn.microsoft.com/power-bi/create-reports/sample-datasets#updated-samples.

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, or the popular keyboard shortcut to run queries, F5 (also added in December!). You can also use the maybe lesser-known keyboard shortcut, CTRL+SHIFT+E to run the DAX query.

Now I can see all the measures in my model, their expression, and other useful information about them. It also gives me their home table but in maybe a slightly less useful TableID column. Thankfully, there is an INFO.TABLES DAX function which has the name of table 10 and 13. To join them, I utilize SELECTCOLUMNS and NATURALLEFTOUTERJOIN shown in the example below.

EVALUATE
    VAR _measures = 
        SELECTCOLUMNS(
            INFO.MEASURES(),
            "Measure", [Name],
            "Desc", [Description],
            "DAX formula", [Expression],
            "TableID", [TableID]
        )
    VAR _tables = 
        SELECTCOLUMNS(
            INFO.TABLES(),
            "TableID", [ID],
            "Table", [Name]
        )
    VAR _combined = 
        NATURALLEFTOUTERJOIN(_measures, _tables)
    RETURN
        SELECTCOLUMNS(
            _combined,
            "Measure", [Measure],
            "Desc", [Desc],
            "DAX Formula", [DAX formula],
            "Home Table", [Table]
        )

Run this DAX query to see the measure, description (if available — I added some for this example), DAX formula, and home table of all the measures in the model.

Documenting your model just got a lot easier! Copy the results where you need with the Copy button.

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

I can then add a Report page with a visual showing information about this model:

And here I have pasted them into Excel and made a dynamic table with CTRL+T.

Copy then paste may not work well if you have DAX formulas on multiple lines in your measures. In that case, you can run the DAX query in Excel directly.

1. Open Excel and navigate to the Data
2. Click Get Data.
3. Click Database in the drop down, then SQL Server Analysis Services (not the Analysis Services one).
4. Back in Power BI Desktop go to Model view, then Data pane > Model, and click the Semantic model. Don’t see it? Make sure Model Explorer is enabled in your Preview features section of Options.
5. In the Properties pane click the copy icon next to Server to add it to your clipboard (also new in December!)
   
   
6. Back in Excel, paste the localhost:xxxxx (most likely it will be a different number for you) into the Server
   
7. You also need the Database, which is not in the Properties pane yet with Server (planned). You can get the Database ID from an external tool, such as DAX Studio, or another free Microsoft tool, SQL Server Management Studio (change Server type to Analysis Services). The database ID is not the name of the PBIX file, but instead a GUID. If you have previously published the PBIX to the Power BI service, the Database GUID is also in the URL of the published version.
   
8. Finally, back in Excel, copy in the DAX query to the DAX or MDX query box and click OK.
   
9. Then click Load.
10. The data will show in an Excel table.
    

What is useful about this approach is you can now refresh it if you make an update to your model during this editing session. When you close Power BI Desktop it will not refresh. But if you re-open Power BI Desktop, you can simply edit the connection with the new Server to refresh it while Power BI Desktop is running. Also, once published to the Power BI service you can edit the connection’s Server to be the Workspace connection and refresh it too. To change the Server, right-click the table, click on Table, then Edit Query. A familiar Power Query window will open, and you can update the Server and Database there.

You can do this with any of your DAX queries, not just these new INFO DAX functions.

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

And when I run this DAX query, I can see information about all the columns in my model. I can see if the name is different than the source name, if they have a data category, and even the DAX formula for calculated columns!

I can take this even further. I am concerned about the number of calculated columns, so I want to see how many there are and then show only calculated columns. I can do this by adding this to my DAX query:

EVALUATE
    {COUNTROWS(
        FILTER(
            _result,
            [Column Type] = "Calculated column")
    )}
    
EVALUATE
    FILTER(
        _result,
        [Column Type] = "Calculated column")

Changing the DAX query view result grid to the 2^nd result, I can see there are 6 calculated columns:

And the 3^rd result shows me just the calculated columns:

I wonder what DAX formula for Category (clusters) 2 is. I can hover over it to see the full DAX formula.

As you can see, there are many new ways to explore the metadata of your model with these INFO DAX functions!

One thing you cannot do with these functions is use them in calculated tables, calculated columns, and measures. They will show an error if you try. So, you should not see them in DAX formula bar IntelliSense. (Edit: the INFO functions will show in the DAX formula bar IntelliSense, but they may result in an error when used). If you use them in the DEFINE MEASURE, DAX query view will currently still show the Update model CodeLens, but it will result in an error when clicked. These measures can still be run as DAX query in DAX query view without error.

Finally, an exciting update to the Update model CodeLens! Now comments are kept when using the CodeLens and the CodeLens will show when the DAX formula formatting has been changed. For example, when you use Quick queries to Evaluate and define a measure, then click Format query. Now those with new formatting will show a CodeLens to Update model to quickly format your measure DAX formulas.

I hope these examples help you with your semantic models. Try out these exciting changes today and let us know your feedback as we continue the public preview of DAX query view and continue to add functionality.

Next steps:

• Share feedback on DAX query view.
• Learn more about DAX query view.
• Read the deep dive blog to get the most out of DAX query view.