5 Tips for Power BI/Analysis Services to improve the use of Excel

Wow, you may recall our previous post ‘5 tips for Excel users when connecting to Power BI’, its popularity needed us to upgrade our webservers. Luckily we run on azure and could easily scale up  Don’t fear, we are more than happy to provide you with five (5!) additional tips to improve your BI environment. In this blog post, we’ll show you different ways to optimize the user-experience for your Excel users focussed on tips and tricks for Power BI report creators and SQL Server Analysis Services developers! 

Power BI Tip 1: Create (a lot of) Measures

Have you ever noticed that there is a different experience when creating reports using PivotTables in Excel and creating reports in Power BI? In Excel you can only work with those measures that you’ve explicitly defined within your Power BI Report. Unfortunately, the ‘implicit measures’ where you drag an attribute in your chart and change the aggregation type (min, max, count, count distinct, …) cannot be used in Excel. Hence, each aggregation that a business user wants to use in its Excel report needs to be written in DAX beforehand, which is a nice incentive to start working on those DAX skills.

Power BI Tip 2: Folders in Power BI

Since a few months already, Power BI now also supports the creation of folders. Hence, design a well-arranged folder structure to locate your measures in. Here are three of the lesser known magic tricks when working with folders:

  • Use a backslash ‘’ if you want to create a subfolder.
  • Use a semicolon ‘;’ if you want to locate the same measure in multiple folders at the same time.
  • The backslash and semicolon can be used together!

While the tip above allows you to store the same measure in multiple folders, these folders always need to be stored on one and the same table.
This tip also applies when designing Tabular cubes. 

While the tip above allows you to store the same measure in multiple folders, these folders always need to be stored on one and the same table. This tip also applies when designing Tabular cubes.

Power BI Tip 3: Use Measure Tables

Another aspect of a different user-experience between Power BI and Excel is the location of your measures. While Power BI allows you to store them in whatever table you like, Excel is stubborn and shows the measures on top of your field list. 

If you want to provide business users with the same experience for both tools, there is a workaround! If you store your measures in a table where all columns are hidden, the icon of your table will change to be a ‘measure table’ and will now always show at the top of your field list! Hence, users will now see measures on top of the field list in both tools Power BI and Excel.

Do mind, applying this method will alter the ‘show-details’ functionality for Excel and Power BI files. Up to you to decide the impact of this trade-off. Storing your measure in a completely unrelated table has no performance impact. You could even make different measure tables to make a distinction between the different functional domains of your KPIs.
This tip also applies when designing Tabular cubes.

Power BI Tip 4: ‘Force’ as Date Table

Date dimensions not only are an added value within Power BI, but also in Excel. It allows your business users to apply some more advanced filters which contain time intelligence; filter on the current month, current year, current year or date periods.

In data warehousing scenario’s, you’ll often work with dummy date values. For fields where you would expect a date but none is provided, a default value ‘1900-01-01’ fills-in the empty space to show that it is not known. Your date dimension then contains all dates of the applicable years and this one default value. A few moments later, you’ll notice that Power BI doesn’t allow you to mark this as a date table as this table doesn’t contain full years.
Follow these steps as a nifty workaround:

1) Import your date-dimension but filter-out the dummy value.

2) Mark your table as a date table (logically this works as you’ll have complete years).

3) Remove the filter of the dummy value, you’ll notice that your date dimension continues to work despite it containing incomplete years!

This tip also applies when designing Tabular cubes.

Analysis Services Tip 1: IsAvailableInMdx

Looking for a quick win to speed-up your Tabular cube processing time? Wanting to reduce the amount of RAM used by your Tabular model? If there are attributes that are not used in any Excel workbook (… such as the attributes that your measures are referring to), then update the ‘IsAvailableInMDX’ setting of these attributes to False! This will reduce the size of your tables and improve processing time.

Wrap-up

Thanks for reading through this blog post, we at Lytix hope you’ve learned something. If you need advice on your BI landscape, send a message to Lytix or the author Sander Allert and we’ll be more than happy to introduce ourselves. 

Sander Allert

Sander Allert is an experienced BI architect with a passion for following new trends. Sander is passionate about data in all of its aspects (Big Data, Data Science, Self-Service BI, Master Data, …) and loves to share his knowledge. Do you need help on architectural decisions, do not hesitate to invite Sander over for a coffee to share some ideas.