Using the new Info functions to provide extra information for the consumers and creators

When the DAX Query View went to public preview in November, I think my second query was trying to get the DMV for the measures in my model. But at that moment, they were not available. But to complete this year, the Power BI team released the Info Dax functions!

In this blog post, I will show you how this feature can be implemented to help your report consumers with a dictionary and your report creators (that use the corporate data model) understand the measures they can use. Let’s get started!

In my report, I add some simple data:

And create two straightforward measures:

€ Sales = Sum(Sales[Sales])
# Sales = COUNTROWS(Sales)

In the model view, I add a description to both of the measures, looking something like this:

Now, when you hover over the measure it gives you the description of the measure. But for report consumers this isn’t enough. They can’t see the measure list. And for a report creator, the description might be a little too vague, especially with very complex measures. So we need to have the DMVs and load that data too!

Getting the info about your data model

In the model view, go to Model (instead of Tables, on the right) and select “Semantic model”. Here you will find the information about your data model, including the name of the server! Also added in December:

Now copy the server name, open for example SSMS, and connect to the Analysis server:

After you connect, expand the databases, and here you find the GUID for the database that runs your Power BI report. Copy this GUID as well, and let’s go back to Power BI! Add a new source, and select the “SQL Server Analysis Services database”:

Connect to your localhost and database, and fill in this query (Can also be found in Zoe’s blogpost):

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] )

This query combines the list of tables with the list of measures and joins them together. Something that wasn’t possible with the normal DMVs, which makes our lives so much easier! After loading the data, my table looks like this:

And add this information in a table on my report:

Now what if I create another measure? It’s very simple. Reload the query and the measure is added!

Now, when we publish the report, we’re going to run into some trouble. When I want to refresh my dataset, I get the following error:

Missing credentials? Let’s go to settings and change this! But I cannot do this:

Because of the Localhost, I cannot reload my data model. So I would need to do this manually on my desktop, and then publish again. We’re not going to do that..

Another option is to create a separate report that connects to the localhost and remove the localhost query from our report. Now, I can refresh the main model and must keep a separate report for the explanation. So, every time we create a new measure, we manually need to refresh the extra report and publish it. And not only that. We also need to change the connection string, because when you close the report and open it again, you will see that the Server and Database GUID changes. Luckily that is very easy to change and will add not more than 5 minutes to your work. But there is a better solution, for everyone that has access to the XMLA endpoint!

If you are lucky enough to have a Fabric capacity, Premium capacity, or Premium Per User license, you can enable the XMLA endpoint. In the workspace settings, go to the workspace connection:

When you go back to SSMS and create a new connection to the XMLA endpoint, you can find your databases listed there:

In Power BI, go back to your original report and create a new connection to a “SQL Server Analysis Services database”, but instead of the Localhost, we use the XMLA endpoint and the database name instead of the GUID.

And now we have our data back in the report again:

What if I create a new measure? Refreshing the table won’t help:

Because our query looks at the data model which is published to the workspace! So, let’s publish to the workspace. After publishing, make sure to refresh the data model one more time, even when it looks like that already happened because you published it. When you open the report, at least in my case, I still don’t see any update, my fourth measure is not there. But after hitting the Refresh Visuals button:

I think that has something to do with the cache of your browser, but I’m not completely sure about that one.

And that’s how you do it! You can now create multiple reports or pages for the different roles of your users. A glossary with an explanation about all the measures and tables, or an easy way for your report creators to see all the DAX formulas without the need to give them actual access to the desktop file.

And yes, those with access to XMLA are luckier, but nothing is impossible for the Pro users!

Take care.

Originally published at http://sidequests.blog on December 26, 2023.