To get the most value out of your data, sometimes it’s a good idea to get little extra help. We’ve prepared some tips and tricks you can use while working with Power BI. 

Tricks

1. Prepare for the transition to Production.  

The Pro version which costs $ 10 per month, does not mean that you no longer need to buy Capacity. This means that you were given a certain number of free tokens to access Power BI, which is supposed to cover the development stage. Typically, the amount of the tokens is enough for 2-4 months of usage, then Microsoft makes you switch to Power BI Premium. 

Solution: this is the most important point, read the details in "Appendix A". 

tinymce loader

2. The problem of sorting the chart’s legend.  

In Power BI you cannot control the sorting of the chart’s legend. Power BI uses the alphabetic sorting by default. 

Solution: before the legend’s name which is displayed on the chart, you need to add a number (1: 2: 3 :). This way you can control the sorting programmatically. Recently, you could use spaces for sorting items. However, this feature was removed.  The method with spaces works in the reports as well - the more gaps before the product, the higher it will be in the report.  

tinymce loader

 

3. How to transfer Power BI data if you have several environments? 

Solution: All reports and dashboards must be stored in a separate workspace for each environment. If there are more than 100 reports in your workspace, to transfer them manually becomes problematic. To automate this process, you can use a ready Powershell script that uses Power BI. Note: if reports were created in the Power BI web portal, you can copy workspaces only in the same account. 

tinymce loader

4. The problem of multi-page reports.  

Power BI supports multi-page reports, but there is no flexibility in it. We cannot control how many pages we want to show in this report. However, you may use a workaround by hiding the navigation bar and putting the pages you would like to show on your web page. See the online live demo. 

tinymce loader

5. The problem of updating datasets using the Power BI API.  

There are the following methods in the API's: to clear dataset, fill with data, update dataset, to refresh and so on. However, the update does not delete the data and does not overwrites the old datasets with new ones but adds it to the existing datasets. Which means it will not work for the filters. 

Solution: you will have to delete the data from the dataset and then fill it with new data. If several users want to do this action simultaneously, there is a chance that one of them will receive either another person’s data or a blank page. 

tinymce loader

Tips

1. If we have used the API (not desktop version) while creating the dataset and the report based on it. Then, this report cannot be downloaded to the desktop (the download button is not active). 

2. If the datasets and reports are in the default workspace My workspace: they cannot be accessed via the API. 

3. If the dataset is created with the help of a desktop application, it cannot be accessed via the API. 

4. When pushing data into the datasets, consider the following limitations: 

  • 75 max columns 
  • 75 max tables 
  • 10,000 max rows per single POST rows request 
  • 1,000,000 rows added per hour per dataset 
  • 5 max pending POST rows requests per dataset 
  • 120 POST rows requests per minute per dataset 
  • If the table has 250,000 or more rows, 120 POST rows requests per hour per dataset 
  • 200,000 max rows stored per table in FIFO dataset 
  • 5,000,000 max rows stored per table in 'none retention policy' dataset 
  • 4,000 characters per value for string 

5. Power BI report is inserted into the page as an iFrame, meaning that we cannot add custom styles or some other code/text to it. 

6. Power BI reports can be exported and integrated into: 

  • PowerPoint (no more than 15 slides) 
  • Excel (only for visual) 
  • SharePoint Online 

Unfortunately, PDF is not supported. 

The printing of reports does not work with multi-page reports (only the first page gets to the print). 

Appendix

The cost of Power BI 

Now we use Power BI Pro, which costs 10 $ per month for each user (we only need to have one user), this week we got a message that we have 50% free tokens. After clarifying the situation, we've got known that the user's tokens are running out and Microsoft is making us switch to Power BI Premium (they changed the pricing policy earlier this year). The minimum fee is approximately $ 750 monthly.  Having studied the situation we concluded to have 3 strategies. What can we do for Production: 

1. Switch to Power BI Premium 

Pros

  • Scalable solution (we are not afraid of any load surges). 
  • There is no need to spend developer's time on maintenance (having read other strategies should be clearer). 

Cons

  • High costs for starting the project (no less than $ 735.92 per month) 

2. Continue to use Power BI Pro 

Every 3 or 4 months you will have to register a new user in this system to download the reports. This can be done by modifying the user data in the config file. We have checked, this solution works. 

Pros

The lowest fees ($ 10 per month + 2 - 4 hours developer every 3-4 months) 

Cons

  • It is necessary to control how many tokens are left (you can automate it) 
  • When the load increases, 3-4 months can turn into 1-2 weeks 
  • A programmer needs to support the system regularly  

3. The ongoing usage of Power BI Pro. Create a user in Power BI and Azure (you can automate it), for each user of our system

Pros 

  • Fees depends on load ($ 10 /per month * users + maintenance price, which depends on a user's activity) 
  • No threat of load surges 
  • Tokens for one user can last for a year (we almost cease to think about it) 

Cons

  • It is necessary to control how many tokens are left (you can automate it) 
  • This strategy is used by many customers (according to forums), but there will be a time when you need to create new accounts for each user of the system