Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

DataZoe
Microsoft Employee
‎12-21-2023 08:56 AM

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.
# Function
1 INFO.ALTERNATEOFDEFINITIONS
2 INFO.ANNOTATIONS
3 INFO.ATTRIBUTEHIERARCHIES
4 INFO.ATTRIBUTEHIERARCHYSTORAGES
5 INFO.CALCULATIONGROUPS
6 INFO.CALCULATIONITEMS
7 INFO.COLUMNPARTITIONSTORAGES
8 INFO.COLUMNPERMISSIONS
9 INFO.COLUMNS
10 INFO.COLUMNSTORAGES
11 INFO.CULTURES
12 INFO.DATACOVERAGEDEFINITIONS
13 INFO.DATASOURCES
14 INFO.DELTATABLEMETADATASTORAGES
15 INFO.DETAILROWSDEFINITIONS
16 INFO.DICTIONARYSTORAGES
17 INFO.EXPRESSIONS
18 INFO.EXTENDEDPROPERTIES
19 INFO.FORMATSTRINGDEFINITIONS
20 INFO.GENERALSEGMENTMAPSEGMENTMETADATASTORAGES
21 INFO.GROUPBYCOLUMNS
22 INFO.HIERARCHIES
23 INFO.HIERARCHYSTORAGES
24 INFO.KPIS
25 INFO.LEVELS
26 INFO.MEASURES
27 INFO.MODEL
28 INFO.OBJECTTRANSLATIONS
29 INFO.PARQUETFILESTORAGES
30 INFO.PARTITIONS
31 INFO.PARTITIONSTORAGES
32 INFO.PERSPECTIVECOLUMNS
33 INFO.PERSPECTIVEHIERARCHIES
34 INFO.PERSPECTIVEMEASURES
35 INFO.PERSPECTIVES
36 INFO.PERSPECTIVETABLES
37 INFO.QUERYGROUPS
38 INFO.REFRESHPOLICIES
39 INFO.RELATEDCOLUMNDETAILS
40 INFO.RELATIONSHIPINDEXSTORAGES
41 INFO.RELATIONSHIPS
42 INFO.RELATIONSHIPSTORAGES
43 INFO.ROLEMEMBERSHIPS
44 INFO.ROLES
45 INFO.SEGMENTMAPSTORAGES
46 INFO.SEGMENTSTORAGES
47 INFO.STORAGEFILES
48 INFO.STORAGEFOLDERS
49 INFO.TABLEPERMISSIONS
50 INFO.TABLES
51 INFO.TABLESTORAGES
52 INFO.VARIATIONS
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.

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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.

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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.

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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.

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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!)
    DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions
  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.
    DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions
  8. Finally, back in Excel, copy in the DAX query to the DAX or MDX query box and click OK.
    DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions
  9. Then click Load.
  10. The data will show in an Excel table.
    DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions
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!

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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 2nd result, I can see there are 6 calculated columns:

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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.

DAX_query_view_introduces_new_INFO_DAX_functionsDAX_query_view_introduces_new_INFO_DAX_functions

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:

0