Using a Power BI perspective with Analyze in Excel

The ability to use perspectives in personalized visuals was added to Power BI in the August 2020 update. Perspectives are a fantastic way to allow your users to work with your data model without overwhelming them with every possible column and measure in the model. Instead, you can define more manageable subsets of columns and measure for them to work with.

As of August 2020, perspectives can be used by your users when they are working with personalized visuals. Another place you may want to make use of the perspectives you define, though, is in Analyze in Excel. It turns out that this is possible, though not very intuitive.

To use a perspective for a data model with Analyze in Excel, first follow the instructions in the official documentation and have your Excel file with the connection to your model open. With the Excel file open and connected to the model:

1. Open the Connection Properties.

2. Click the Definition tab.

3. Manually enter the name of the Perspective in the Command text box. This is the part that is unfortunate. We don’t get a drop-down to show us the available perspectives, so we have to know the name of the perspective and manually type it in (but it’s not case-sensitive, so that’s a bonus).

If you type in the name of a perspective that does not exist in the model, you’ll get an error message:

After clicking OK in the error message, the connection resets back to the last perspective that worked. The default is “Model.”

4. Click OK in the connection properties dialog.
5. Verify that the model is now showing only the columns and measures in the perspective.

Ta-da! Your users can now take advantage of the simplified perspectives you’ve created for them while using Analyze in Excel.

Double, double toil and trouble;
Fire burn and caldron bubble.