3 Challenges with Power BI (and how to overcome them)
Microsoft Power BI is a valuable tool in your BI solution suite. It is effective in transforming the way industries leverage data to solve problems, share insights, and make informed decisions. I’ve been working with Power BI recently, and although it appears to be a great tool, it is not without its quirky challenges. I’m going to walk you through 3 challenges I’ve faced recently that could impact the effectiveness of this powerful tool.
Challenge #1: Import Mode vs. Direct Query Mode
In Power BI, if you load the data using import mode or direct query mode, you must then stick with either of the modes throughout the report; you cannot switch to the other. However, with the release of the latest version in July, Microsoft added the capability of using composite model, which means part of your data source can be with import mode, and another part can be with direct query mode. This saves the developers and users time by not having to reload or modify the complete data source. To activate this feature, you must enable the Composite model preview feature in the Power BI Desktop options. Your Power BI file would need a restart after this action:
Challenge #2: Using colors beyond the default theme
Power BI comes with default color theme with a predefined set of colors that user can choose., However, if we need a new color other than available default colors, we must manually place the relevant hex color code from the custom color section. The disadvantage with this option is we cannot easily reuse this color in a new report. The user needs to recreate the color manually again when he creates a new report. To solve this problem, we can create a custom color theme file using simple JSON script. Below is the sample script with hex color codes:
“name”:”Sample Color Theme”,
“dataColors”:[“#B32035”, “#0D3E5C”, “#1F72A8”, “#3599D4”, “#34474F”, “#9CA0A1”]
The “name” option gives a default name to the theme so that we can reuse the color based on the name. Leveraging the “data colors” option saves the colors based on the given hex color codes. Once you create the script, save the file using the JSON format. You can now import the color theme file from Power BI desktop using the switch theme option.
After importing the file, you can navigate to data colors section to check the list of colors that we loaded. If you want to revert to default Power BI color theme, you can select the Default Theme option.
Challenge #3: Power BI Report Data Load/Refresh is very slow
Frequently, we observe that some of our Power BI reports take significant amounts of time to load/refresh. This can happen when we have data coming from a large number of tables into Power BI. The impact is magnified if there are many table joins. The result is the data load or refresh times can be very slow. Not good. To overcome this problem, try following these steps:
- determine if you have duplicate tables and remove them
- avoid loading unnecessary tables into the report
- check if all the fields from the tables loaded are necessary
- restrict performing calculations and joins on the database side instead of inside Power BI.
There is another option in Power BI where we can combine columns from multiple tables into a single table using a DAX table formula. Here is an example:
CustomerRecords = SELECTCOLUMNS(Customers,”ContactName”,RELATED(Person[Name]), “Orders”,RELATED(‘OrderName'[Orders]),”Price”,RELATED(‘Product'[Price]),”Amount”,Records[Amount],”Total”,[Amount]*RELATED(‘Product'[Price]))
We can create Table formula by navigating to Modeling -> New Table
Data Analysis is critical to any business success, and Power BI is a solid tool to unlock the power of your data. However, it can also be a cumbersome, sometimes frustrating process to extract meaningful insights with these kinds of challenges. Overcoming just a few of these challenges in Power BI could help improve both the adoption and value of your business intelligence investments.