Hidden features of Analyze in Excel – Part 1 – Show me the details

Why focus on Analyze in Excel?

In my previous post about Organizational Data Types I introduced this chart showing the potential reach of certain features in an organization. There we covered the widest reaching feature.

Organizational sphere of influence

In this series I would like to cover some hidden features of Analyze in Excel. These will improve the usability of your solutions and solves some of the issues you would face on your adoption journey to Power BI. There is an Adoption Roadmap available that also talks about the challenges.

Not everyone will want to use Power BI in an organization, but that does not mean we should not focus on them too. We need to reach people where they are currently and not necessarily force them to change.

Show Details

In a Pivot table there is a standard feature called Show Details. This gives a filtered view of top 1000 rows based on our selection.

Show Details functionality in Excel
Show Details functionality in Excel

While it works with standard pivot tables by default, this behaviour changes when we use Analyze in Excel.

Data Model

First let’s understand the data model we are going to use with Analyze in Excel. This is a fair straightforward subset of a bigger model.

  • One fact table – Housing Market Data
  • Three dimension tables – Calendar, Post Code, Lower Super Output Area
  • Calculations table that only contains measures organized into folders (following Power BI data modelling best practice)
Data model
Data model

Standard experience

Let’s see what happens when you try to use Analyze in Excel with this standard model.

After connecting to the published dataset, it is straightforward to use the pivot table as expected. If you want to see the details, you can double click on the cell…

Click to Show Details from a cell in a Pivot table
Click to Show Details from a cell in a Pivot table
Pivot table fields
Pivot table fields

…or right click and select Show Details

Show Details option
Show Details option

What you would expect is a nice table of columns from the Housing Market table with the filtered rows. This is how it works with simple Excel pivot tables.

No data shown - measure table is empty
No data shown – measure table is empty

Instead, this screen is going to welcome you. But why?

Because we are using a Power BI data model, the measures are in their own data table, which does not have any columns or rows.

What if the measures are in the fact table?

We would still have a problem because it would exclude all the relationship columns like date and postcode in our example, losing valuable information from the details.

So, using Power BI have some limitation for Excel users?

How to see the details – Detail Rows Expression

To solve this issue and even provide better results we need to use Tabular Editor.

The rows that are displayed on Show Details are dependent on the measures. To determine which columns should appear we need to provide a Detail Rows Expression.

Detail Rows Expression in Tabular Editor
Detail Rows Expression in Tabular Editor

Because we are using a data model, we are not just limited to columns from one table, but any related information can be added to it.

You will see a sample Dax query that I used for this blog post. The best way to design a query for your data model is to use DAX studio.

Detail Rows Expression sample:

SELECTCOLUMNS(
'Housing Market Data',
"Transaction ID", [Transaction ID],
"Estate Type", [Estate Type],
"Property Type", [Property Type],
"New Build",[New Build?],
"Local Areas District", RELATED('Local Area District'[LA Name]),
"Post Code",RELATED('Post Code'[Post Codes]),
"Street",[Street],
"House Number/Name",[House Number/Name],
"Transaction Date",RELATED('Calendar'[Date]),
"Properties Sold", [Properties Sold],
"Average Price Paid", [Average Price Paid]
)

Default expression

If you have a lot of measures it could seem like a lot of work to add this to each of the measures. If you would like the same expression for all the measures in a table, then you can use the Default Detail Rows Expression setting for the table.

Table level Expression

You are still able to add specific expression to certain measures if they need to differ.

Summary

Using Detail Rows Expression, you can ensure that the Excel users do not feel that they lost a valuable feature because of Power BI, instead they not have an even better version of that.

Hope you like this blog post, the next part will arrive shortly.

Please follow and like us:

About the author

Mihaly Kavasi

Data-driven Decision-Making Enthusiast.
Power BI Expert and Trainer. Helping organizations and communities to use data effectively.
Big fan of RE and Electric cars.
FTRSA, MCSE, MCT
Working @ Avanade

View all posts