Save yourself time working with Power BI.
Don't repeat yourself (DRY) (https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) is to avoid duplicating effort. Power BI offers opportunities to be WET ("write everything twice", "we enjoy typing" or "waste everyone's time"), five ways to get DRY with Power BI.
Create a single semantic layer.
Power BI report writers interact with the semantic layer to produce visualisations. A semantic layer has the properties:
Do you have a wild-west scenario where people publish independent datasets to Power BI, reporting the same data? You will get these problems:
SOLUTION: Design a single semantic layer using Power BI or Azure Analysis Services.
Power BI:
Azure Analysis Services:
Add row-level security to save hours.
Row-level security allows you to release the same Power BI content, only exposing a filtered slice to each user. The cumbersome alternative is to duplicate reports for each department.
Hit manage roles to add Power BI row-level security:
You then add users to roles in the service:
Add row-level security to Azure Analysis Services and not Power BI if row-level security needs to apply to report creators. Row-level security is by-passed by having edit permissions on an app workspace in the Power BI service or by using Power BI desktop.
SOLUTION: Add row-level security to Azure Analysis Services. You then apply row-level security to report readers, report creators, and all reporting tools not just Power BI.
Think folders, not files when importing customer feedback csv files or monthly sales Excel spreadsheets.
Power BI has a data transformation tool built-in. Import many files of similar structure as a single entity.
SOLUTION: Import a folder (Get Data | Folder). Hit the combine button to append all files into a single table. Apply further transformations and report on the data as a whole.
Your solution even imports from sub-folders and files added in the future. You make huge time savings when adding this automation.
Use explicit not implicit measures.
Using implicit measures in Power BI is possible. An example is the Discounts column in the Sales table below. You drag Discounts (Sigma icon) on to the reporting canvas to visualise it on a card.
Implicit measures have the following limitations:
The last point is very important and worth an example. Sales = Gross Sales - Discounts, we create a DAX measure for this:
Sales Sum WET = SUMX(Sale,Sale[Units Sold] * Sale[Sale Price]) - SUM(Sale[Discounts])
Alternatively, we define explicit measures for each part:
Gross Sales Sum = SUMX(Sale,Sale[Units Sold] * Sale[Sale Price])
Discounts Sum = SUM(Sale[Discounts])
Now our Sales measure is:
Sales Sum = [Gross Sales Sum] - [Discounts Sum]
[Discounts Sum] is now defined once, and used everywhere.
Define the logic for each measure at a single location. Any change is done once and flows throughout your application.
SOLUTION: Create explicit DAX measures. Assign an unambiguous name, apply formatting and use the full power of DAX to create more complex measures. Reference measures in other measures to encapsulate logic in a single location. You make future bug fixes or change requests in one place, truly matching our requirements not to repeat ourselves.
Don’t add DAX calculated columns (unless you have to).
You can add DAX calculated columns when the attribute does not exist at the source. Examples:
Calculated columns are not DAX measures. Always create a measure when performing an aggregation. Calculated columns are computed at data refresh and stored in the data model.
Change the data source to add the column where possible. Add a table column or update an abstraction layer view adding the attribute. The column is now available for all and logic does not have to be repeated.
Data compression is also more efficient when importing a column rather than adding via DAX.
SOLUTION: Add a required column at the source. You define this logic once, allowing its use everywhere in any reporting tool. You achieve better storage compression and don’t have to repeat logic. Go ahead and add using DAX if you don’t have source permissions and any alternative.
Business intelligence solutions are not fire-and-forget. They evolve and the following change:
You will be making regular changes, so make your life easy. Use the five points in this post to design a flexible solution that is a pleasure to maintain.