Power BI Insights Week 5/2023

Welcome subscribers,

We are now approaching 19,000 subscribers ??

I've changed the format of the newsletter.

Each week I will share up to 10 Power BI related content with summaries and links - they will include a mix of videos and articles.

Later on, I will also include LinkedIn posts that I found useful relating to Data.

If time allows, I plan on publishing the newsletter each Tuesday.

Here are some of the latest insights on Power BI related content for this week's newsletter.

1. Handling Errors in Power BI: Try & Otherwise Power Query // IFERROR DAX Function

In this video, the speaker explains different ways to handle errors in Power BI. He demonstrates how to handle errors in Power Query using the "try" and "otherwise" expressions and in DAX using the "IFERROR" function. He also explains how to deal with errors in data and programming in general, and how a single error in Power BI can cause issues in a report. He shows how the "try" expression can be used to try a calculation and if it returns an error, the "otherwise" expression can be used to specify a default value, like "null", instead of breaking the whole report. In DAX, the speaker demonstrates how to handle errors by using the "IFERROR" function to return a default value in case of an error, which can avoid the frustration of a complete error of the calculation.

Key takeaways

1. Handling errors in Power BI is important to avoid issues like dashboard not refreshing or calculations not showing a value.
2. There are two ways to handle errors in Power BI: using "Try & Otherwise" in Power Query and the "IFERROR" DAX function.
3. The "Try & Otherwise" expression in Power Query allows you to try an expression and if it returns an error, you can specify the value to return instead (e.g., null).
4. The "IFERROR" DAX function allows you to handle errors in a calculation by specifying the value to return in case of an error.
5. The "Try & Otherwise" expression provides a detailed error record that can be useful when troubleshooting multiple errors.

2. Considerations For Dark Backgrounds In Power BI Reports

The speaker, Sam Makai from Enterprise DNA, is discussing considerations for using dark backgrounds in Power BI reports. He highlights the importance of ensuring the visualizations have a background within them when using a dark background in the report. If the background in the visual is not set, the numbers in the reports will not be visible when drilled into. The speaker recommends using a grayish background with some transparency, or a full color background, within the visualizations to make sure the numbers are visible. The speaker emphasizes the significance of this consideration when using dark backgrounds and says it is key to making the visualizations stand out and be effective.

Key takeaways

1. When using dark backgrounds in Power BI reports, it is important to ensure that the visuals within the report also have a background to prevent the numbers from being invisible.
2. It is recommended to have a grayish background with a bit of transparency within the visuals to integrate well with the dark background.
3. Ensure that the visuals have quality backgrounds to show labels, data labels, and categories when expanded.
4. Darker backgrounds make visualizations stand out and are recommended to implement best practices for visualizations.

3. What happens when you miss a refresh day with Power BI Incremental Refresh?

The video explains what happens when you miss a refresh day with Power BI Incremental Refresh. The speaker sets up a data set with incremental refresh, with daily refreshes of the data set. He then demonstrates what happens when the refresh fails for a few days and then starts succeeding again. He explains that incremental refresh is self-healing and will create the partitions for the days that it failed to refresh and populate those partitions. He also mentions that this behavior holds true for months and quarters but not tested for years. The video ends with a call to action to post questions and comments in the comments section below and to subscribe to the channel.

Key takeaways

1. Power BI Incremental Refresh is capable of self-healing.
2. When a refresh fails, the incremental refresh will go back and create the partitions for the missed days and populate those partitions.
3. This self-healing behavior holds true for missed months and quarters, but not for years.
4. The speaker invites viewers to post questions and comments in the comments section.

4. Publishing a Public Power BI Report from Google Sheets

- Power BI is a data visualization tool that allows for interactivity to help analyze data.

- The author used Power BI to create a report for a data set stored in a Google Sheets spreadsheet.

- The report was created in Power BI Desktop and published on powerbi.com with a free account.

- To share the report publicly, the author had to enable the "Publish to Web" feature in the tenant settings.

- The report was then embedded on a website through a URL or HTML code.

- The author concludes that the process of linking Google Sheets to Power BI and publishing the report was not hard but not intuitive.

Link to article

5. Power BI Tips And Tricks For Your Reports

- Four main groups of Power BI tips and tricks: easy fixes, context, clarity, and strategy.

- Tips to improve the quality of reports:

- Disable Y axis if data labels are enabled

- Disable visual headers when not in use

- Disable button tooltip when not used

- Avoid scroll bar in tooltips

- Be careful with interactions

- Brand your report and be consistent

- Keep the layout and designs consistent

- Use templates to maintain consistency and speed up report creation.

Link to article

6. How to get performance data from Power BI with DAX Studio

- Power BI and SQL Server Analysis Services have a Tabular model with two engines: Storage Engine and Formula Engine.

- The Storage Engine is the interface between DAX queries and the data stored in the model, and uses xmSQL to query the data.

- It is multi-threaded and can cache queries and their results.

- The Formula Engine is the DAX engine and executes functions not handled by the Storage Engine.

- It is single-threaded and doesn't have a cache.

- The goal is to offload as many operations as possible to the Storage Engine.

- To get performance metrics, DAX Studio is required and can be installed or used as a portable version.

- In Power BI Desktop, start the Performance Analyzer, record performance data, and copy the DAX query.

- Open DAX Studio, connect it to the Power BI Desktop file, paste the query, and start Server Timings to see the time used by each engine.

Link to article

7. Power BI Data Model Optimization With VertiPaq

- VertiPaq Analyzer is a tool in DAX Studio to extract information about tabular models for optimization purposes.

- To use VertiPaq Analyzer, go to the Advanced tab in DAX Studio and click View Metrics.

- VertiPaq Analyzer Metrics pane provides a list of tables in the tabular model and information about each column, such as cardinality, size, encoding, data type, relationship size, and percentage of RAM consumed by the table.

- The VertiPaq Analyzer can be used to identify necessary and unnecessary columns in the data model, to free up storage space.

- The Relationships Tab allows identifying root cause of RI Violations and missing keys between facts and dimension tables.

- The Summary tab provides basic overall information of the data model, such as total size, row count, number of tables, and columns.

Link to article

8. Build Your Own Power BI Audit Log; Usage Metrics Across the Entire Tenant

- Power BI dashboards and reports come with usage metrics that can be seen in the Power BI Service, but the metrics are limited to the current workspace.

- If you have access to the Power BI Administrator account, you can see the usage metrics across the entire tenant, but it is not customizable.

- To get a customizable usage metrics report for all activities in the Power BI tenant, you can extract the audit log from Office 365 and export it into text files.

- The audit log can be extracted using various methods such as Office365 Audit log PowerShell Cmdlets, Power BI Cmdlets for PowerShell, REST API for Power BI, or a third-party tool.

- The article explains how to extract the audit log using Office365 Audit log PowerShell Cmdlets by installing the ExchangeOnlineManagement module, logging in to the Power BI account, and accessing the entire audit log through the admin account.

Link to article

9. Providing Context In Power BI Reports

- Use titles and subheadings to clarify the report's purpose.

- Make the title stand out and easily readable.

- Add a tagline or short sentences to explain the report.

- Create strong contrast between text and headings.

- Left-align text to make it easier to read.

- Keep the same format for titles, headings, labels, and texts throughout the report.

- Be careful about the tone of the report.

- Provide sufficient context in Power BI to frame the data.

- Compare data to the budget, forecast, and benchmarks for more context.

- Start visual graphs from a zero baseline.

- Pay attention to the context and message you are sending.

- Give context and help the audience connect the dots.

- Use progressive disclosure techniques and effective navigation to keep the user engaged.

- Differentiate between sharing data and sharing insights.

- Show the conclusion analysis you took from the data and make it clear to the user.

Link to article

10. Debugging DAX measures in Power BI

The video shows how to debug DAX measures in Power BI using four different techniques. The first technique involves using variables and the Power BI Desktop editor to inspect the intermediate result of a measure calculation. The other three techniques involve using external tools such as DAX Studio, DAX Debug Output, and the Debugger feature in Tabular Editor 3, which allows for a real debugging session. The video demonstrates how to isolate the result of each step of the calculation and inspect it using these tools to locate problems and fix them.

Key takeaways

1. Four techniques for debugging DAX measures in Power BI are discussed.
2. Power BI Desktop can be used to investigate the intermediate results of a measure.
3. DAX Studio can be used to execute queries and manipulate code.
4. DAX Debug Output can be used to show the output of the evaluate and log function.
5. Tabular Editor 3 has a debugger feature that performs a real debugging session, executing code step by step.
6. A measure called "Delta Average" is not working as expected and the video explains how to debug the problem.
7. Variables can be used to split the calculation into different steps to isolate results.
8. Debugging involves locating the source of an error and fixing the calculation code.

Hope you enjoyed the newsletter.

Learn Power BI for free at Datamastery

Disclaimer: Please note that ChatGTP was used to summarise the content on this page.

#PowerBIByArno #PowerBI4Newbies #PowerBI #PowerBICommunity #DataFam #Data #BusinessIntelligence

Until next time!

Arno Wakfer