Power BI: Tips & Tricks

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 whileworking with PowerBI.

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 PowerBI, 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".

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

In PowerBI you cannot control the sorting of the chart’s legend. PowerBI 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 beforethe product, the higher it will be in the report.

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 aremore than100 reports in your workspace, to transferthem manually becomes problematic. To automate this process, you can use a ready Powershell script that uses PowerBI. Note:if reports were created in the Power BI web portal, you can copy workspaces only in the same account.

4. The problem of multi-page reports.

PowerBI supports multi-page reports, but there is no flexibility in it. We cannot control how many pages we want to showin 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.

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

There are the following methodsin the API's: to clear dataset, fill with data, update dataset, to refresh and so on. However, the update does not delete the dataand doesnot 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 actionsimultaneously, there is a chance that one of them will receive either another person’s data or a blank page.

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 viathe API.

3. If the dataset is created with the help of a desktop application, it cannot be accessed viathe 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

5. PowerBI report is inserted into the page as an iFrame, meaningthatwe cannot add custom styles or some other code/text to it.

6. PowerBI 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 PowerBI.

Now we use PowerBI 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 have3 strategies.What can we do for Production:

1. Switch to PowerBI 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 startingthe project (noless than $ 735.92 per month)

2. Continue to use PowerBI 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.