Archive for Power BI

Coolest New Power BI Features Revealed!

Recently the Microsoft Business Applications Summit 2019 highlighted new Power BI features and these are the coolest features to note IMO:

1. New Power BI App Workspace Experience in Preview Power BI App Workspaces were introduced to enable collaboration amongst the data/business analysts within an organization. The new experience introduces numerous improvements to better enable a data-driven culture including:

•   Managing access using security groups, distribution lists, and Office 365 Groups
•   Not automatically creating an Office 365 group
•   API’s for Admins, as well as new tools for Power BI Admins to effectively manage workspaces

2. Printing Reports via Export to PDF
You can now easily print or email copies of reports by exporting all visible pages to PDF.

3. Bookmark Groups
Now you have a way to organize bookmarks into groups for easier navigation.

4. Python Integration in Preview
Now data scientists can use Python in addition to R within Power BI Desktop.

5. New Visual Header
More flexibility and formatting options have been added to the header section of each visual.

6. Tooltips for Table and Matrix Vizs
Report page tooltips are now available for the table and matrix visuals

7. Many to Many Relationships in Preview
You will now be able to join tables using a cardinality of “Many to Many” – prior to this feature, at least one of the columns involved in the relationship had to contain unique values.

And now I’ve saved the best for last!

8. Composite Models in Preview
With this feature, you’ll now be able to seamlessly combine data from one or more DirectQuery sources, and/or combine data from a mix of DirectQuery sources and imported data. For example, you can build a model that combines sales data from an enterprise data warehouse using DirectQuery, with data on sales targets that is in a departmental SQL Server database using DirectQuery, along with some data imported from a spreadsheet.

As you can see there are many new features to digest but it would be well worth your while to follow the links provided.

On a closing note, I’d like to give you a teaser for two new features coming up soon that will have a huge impact on self-service data prep and querying for big data:

  • Dataflows
  • Aggregates

Stay tuned!
Mario Di Giovanni, BASc, MBA, CBIP
Director, Business Analytics

More about Mario

 

Custom Visuals in Power BI

Out of the box, Power BI gives you access to many common data visualizations. Sometimes the basic charts don’t get you to where you need to go. Luckily, Microsoft and the Power BI community created custom visualizations that will be available to you. I am going to give you a quick primer on how to access those and then let you know what my favorite visualizations are.
Downloading Custom Visuals
There are two different ways of accessing custom visuals straight from Power BI.

    • On the visualizations pane, you can click on the ellipsis icon and you will see the option to Import from store. Once you click on this a pop-up will appear and allow you to search and add custom visuals. Please note, this method works in both the Desktop application and online with Power BI.

 

 

    • On the Home tab, there will be an option From Store under Custom Visuals. This method is only applicable when using Power BI Desktop.

     


  • Favorite Custom Visuals
    There are many options out there for custom visuals and it is constantly being updated with more. Below are my three favorite custom visuals.
    Bullet Chart by OKViz
    A bullet chart is a great visual introduced by Stephen Few which allows you to show performance against a target. One use of a bullet chart is revenue and expense tracking against budget. This chart allows you to track your target, your actuals, and then qualitative reference bands. The bands can be used to track the number’s state, such as Bad, Acceptable, Good.

    Brick Chart by MAQ Software
    This visualization is a different way to look at percentages. It has 100 squares which are colored by a categorical field depending on the percent of total. For our example below, we are looking at overall Profit by Region and we can see that the Central Region has the highest profit.

    Hierarchy Slicer
    This really isn’t a visual, but a slicer, however it allows you to add hierarchal fields to a slicer and it will retain the hierarchy. For instance, if you used a date field in this slicer, you can have a hierarchy of year, quarter, and month.

    There are many custom visuals available and it can be overwhelming at times. Hopefully this will help you as you start to explore custom visuals.
  • By the way, be sure to checkout last week’s post about the October 2017 release of PowerBI in case you missed that!

Data Storytelling Gets Easier: Power BI Desktop Oct 2017

The October 2017 release of PBI Desktop includes a much-anticipated feature that makes data storytelling easier: Bookmarking. Bookmarking in combination with the spotlight, drillthrough and what-if features allows you to create very compelling stories about your data. It’s currently in “preview” mode, so you have to turn it on by going to the options page. After a restart of PBI, there will be a new Bookmarks Pane available via the View tab.
 

 
You can now set up a report with all the filters that you want and then click the Add button under Bookmarks. This basically takes a “snapshot” of the page that you can refer back to later. The Spotlight feature can be used in combination with Bookmarks to highlight a specific visual on the page. When it’s time to present your story, you can step through all of the Bookmarks to create a powerful narrative.

 
As part of bookmarking, Microsoft also added a new selection pane. This pane lists out all objects on the report page you are currently viewing and allows you to select the visual from here. This is very useful when you are authoring complex reports with many overlapping charts. You can also hide visuals on your report through this pane.
By combining this feature with bookmarking, you can create very interesting reports with visuals changing out depending on the current bookmark selection. You can open the selection pane through the View tab.

 
In the selection pane, for each item on the current page, you’ll see either an eye or line icon letting you know if it is visible or not. Clicking on the eye icon will let you toggle the visual between hidden and visible.
Another feature which I like is that you can now download Power BI Desktop from the Windows Store. This will install the monthly release automatically in the background and there’s no need for admin privileges.
Click here to get a summary of all of the features in the October Power BI Desktop release. And in case you missed our summary of the September 2017 release of Power BI Desktop, go read it now.

Power BI: New Drillthrough and Insights Features from Sept 17

Power BI was launched almost 3 years ago.  It includes a free desktop application for authoring reports and a cloud service for publishing and sharing reports.  Since day one Microsoft promised to deliver an updated version of the PBI Desktop application each month – and they have honored this commitment, which is rare in the space.  Each release includes exciting new features that have been voted on by the PBI community.  Last week the Sept version (26th release!) arrived with a number of key improvements which helps associations understand and access data quickly.  Click here to view the Sept feature summary.
For me, two features, in particular, stand out:  Drillthrough to another report page and Explain the increase/decrease insights (preview).
I’ve been eagerly awaiting the Drillthrough report feature.  It allows you to drill-through from one page to a more detailed page using a filter on the originating page.  For example, let’s say we want to create a dashboard that shows the top 10 customers by customer engagement score.  On one page, we can create a summary visual such as a column chart showing the top 10 customers ranked by score.  We can now right click on one of the customers in the chart and “drill through” to another report page which contains more details about that customer such as contact information, recent orders, recent interactions with our website or whatever makes the most sense.
The Explain the increase/decrease insights feature is very cool. It lets us right click on a bar or a data point in a line chart and explain why the data point increased or decreased compared to the data point before it.  Behind the scenes, Microsoft runs their insights machine learning algorithms over the data to come up with a number of visuals describing which categories most influenced the increase or decrease.
We can then select the visual that best delivers the data story and add it to our report.  Click here if you want to see a video of this feature in action.   It’s currently in preview mode so there will be improvements made over the next couple of months as user feedback is gathered.
Give the Sept PBI Desktop release a try and start drilling and getting insights from your data today!

DAX the Way – with Power BI

DAX stands for Data Analysis eXpressions and it is the formula language used in Power BI.  It’s similar to Excel formulas and it’s an extremely powerful language for creating measures and calculated fields within your Power BI data model.  The downside is that it requires a bit of a learning curve to get up to speed.  There are a lot of great resources out there for learning DAX and to help us along the April release of Power BI Desktop included a preview feature called Quick measures.
Quick measures allow us to quickly and easily create common and powerful calculations.  A Quick measure generates the DAX for us based on the input provided in a dialog box, then presents the results in the report. Best of all, we can see the resulting DAX formula that will be executed, and use that to expand our knowledge of DAX.  Learning by example is always the best way in my book.

We create Quick measures by right-clicking a field in the Fields well, then selecting Quick measures from the menu that appears. Also, we can right-click any value in the Values pane of an existing visual. There are 6 categories of calculation types with a number of calculations for each.  As of July 2017, the Quick measure feature is still in “preview” mode, so to see it, we have to enable it.  Select File > Options and Settings > Options > Preview Features, then select the checkbox beside Quick measures. Restart Power BI Desktop after you make the selection.
When creating a Quick measure, a menu will pop up which guides us through the creation of the specific measure.  It’s a little confusing at first but after a little trial and error things start to make sense.
Another cool thing is that there’s a Quick Measures Gallery in the Power BI community site where DAX experts have shared some advanced DAX snippets.  These community contributions often make it into the Power BI Desktop Quick measures menu in future PBI Desktop releases.
So give Quick measures a try and soon we’ll all be singing DAX the way. Uh huh, Uh huh. I like it!.

How To Choose the Right Visualization

Creating a report or dashboard in Power BI is quite easy.  A few mouse gestures and a couple of clicks and voila, we have a report!
Creating a report that tells a story at a glance, however, is hard.  The ultimate test of a good report/dashboard is if someone without any prior knowledge can quickly understand it. Our audience should be able to see what the page is about and what each visual is about — at a glance!
We also want to consider whether we have more “sizzle” than “steak”.  Is our visualization attractive but without substance?  Don’t get me wrong, “pretty” is important.  We just need to find a balance between “pretty” and “useful”.
Power BI has about 30 built-in visualization types.  Add to that all the custom visuals that we can get at the Office Store and we literally have hundreds of visuals to choose from.  What’s a data analyst to do?
So here’s a one-page cheat-sheet that I use all the time.  It gives us a helping hand when trying to figure out which visual is best for the type of story we are trying to tell.  Here are a few examples:

When we want to display a measure and compare it by magnitude, a Clustered Bar or Column chart will do the trick.  Alternatively, a custom Bullet chart is very effective.  But we probably shouldn’t use a Gauge or Bubble chart.

Clustered Column Chart

Line Chart

Trends over time are best visualized using a Line chart or custom Sparkline.  However, an Area chart is probably not a good choice.
When we want to display a single value, use a Card or KPI visual.

Card

 
This cheat-sheet is a great starting point for telling your data story.  Browsing the Power BI Data Stories Gallery is also a great way to get some inspiration.  I’ll also experiment by switching between visualizations until I find one that best suits the story I’m trying to tell.  The good thing about using Power BI is that it can support this kind of “speed of thought” analysis.
Download this guide and keep it handy when you are trying to put some “steak” into your reporting “sizzle”.

We are overwhelmed by information, not because there is too much, but because we don’t know how to tame it. — Stephen Few

Power BI vs Tableau

I am often asked, “Which tool is better: Power BI or Tableau?”
Microsoft has done a great job with Power BI over the last two years. The features in Power BI have evolved so much that we’re now recommending it first to our clients. Microsoft identified the most important and useful features for a data visualization tool set and embedded them within Office 365. Power BI now has the distinct advantage of being familiar and easily accessible to non-technical users, which improves its adoption rate.  Microsoft's Power BI vs Tableau
In addition, Gartner’s 2017 Magic Quadrant ranks Power BI higher on “Completeness of Vision” than Tableau. However, to be fair, both Power BI and Tableau are matched for “Ability to Execute.” Tableau was first to market and has deeper capabilities, however, in our experience, the advanced functionality offered by Tableau happens to be the functionality that associations use the least.
If you are a power user, you may still want to consider Tableau. But, if you’re responsible for rolling out a new data analytics initiative within your association, Power BI is an affordable option with the added benefit of being “front and center” when staff log in to Office 365.
This list of pros and cons will continue to evolve, so check with us if you want to know what’s new with either solution.

 Power BI

 Tableau

 
Pros

  • Affordable
  • Integrated with O365
  • New features weekly
  • Familiar to Excel users
  • Self-serve data prep
  • Office store apps and custom visuals available
  • Extensive data connectivity
 
Pros

  • Excellent user interface
  • Fast and easy to create visuals
  • Good customer support
  • Story-telling feature
 
Cons

  • Data Analysis Expressions (DAX) knowledge required for advanced calculations
 
Cons

  • Cost
  • Self-serve data prep

Using Dates in Power BI

Analyzing dates in Power BI is quick and easy. There are just a few things you need to know to format dates.
bar chart
When you drag a date field to an axis, it will automatically default to show year. However, you will have the option to drill down on your date field. In the top right of your visualization, you should see two down arrows. If you click those arrows, it will change your visualization to look at Quarter, if you click it again, you will see Month, then Day.

Determine Drill down Options

drill down
What options you have when you drill down is determined by the fields on your axis.  If you do not want to give the option to drill down to day, click on the x next to Day and then you will no longer have the option to drill down to the day level.
axis

Expand Hierarchy

I am sure you noticed that as you drilled down, you would see Quarter only, not Quarter and Year.  If you want to expand your hierarchy, click on the next icon over.
expan
When you are finished drilling down, click on the up arrow and you will be able to drill back up.

Exact Date

The last option for dates is to use to the exact date, instead of a hierarchy.  If you go to the axis and click on the arrow next to your field name, you will have the option to change the field from Date Hierarchy to Date.
exact date
 
I hope this helps you get started in analyzing your time-series data in Power BI.

Build Power BI KPI Visualizations

Building Key Performance Indicator (KPI) visualizations in Power BI is a breeze. KPI visualizations allow you to view a specific measure against a target. The target might be last year’s totals or it could be your goal for this year. The easiest way to accomplish these in Tableau is using a bullet cart which is a very effective way of looking at the data but falls short when your audience just wants to see the numbers big and clear. Let’s look at some out of the box visualization types in Power BI.

Gauge

Using the built-in gauge visualization allows you to quickly show a value (like your current sales). Then you can either use fields for your minimum value, maximum value and target value or just type them in. In this way, you can very quickly build a visualization that shows how you are doing against a predetermined goal.
total sales gauge

KPI

The KPI visualization allows you to have a large text indicator but also show a little more context. To build this, you will need 3 fields: what you want to measure (like your current sales), a field for your trend axis (like the fiscal month) and a field for your target (your sales goal per fiscal month). With these fields in place, the visualization will do a couple things automatically: do color coding on the text and trend to indicate if you have reached your goal or not; show the percentage difference between your actual and your goal.
total sales KPI
These two visualization types are great to use on your executive dashboard to clearly communicate progress against your association or department goals.

Resolve Power BI Column Discrepancy

During development or enhancements to a Power BI solution, you may encounter this message: “An error occurred while processing the data in the dataset. The ‘handouts_path’ column does not exist in the rowset.” You probably received this message via email if you published the dataset.
powerbi_column_discrepancy

What Column Discrepancy Means

The message means that since the dataset was published, the actual data source (SQL Server, Redshift, Excel, etc.) was changed to remove or rename the column. Now, the published data source can no longer find it.
 

How to Fix Column Discrepancy

Here are the few steps to resolve the error message:

  1. Open the PBIX file in Power BI Desktop.
  2. Locate and click the refresh icon in the toolbar, as shown below.
  3. Wait for the refresh screen to complete. This will vary in length depending on the size of the data source.
  4. Once complete, confirm the change in the fields pane to the right. Any field removed should no longer appear and any field renamed should reflect the new name.
  5. Examine existing visualizations and correct any issues resulting from the changed field.
  6. Save and publish the package choosing the replace existing option when prompted.

powerbi_column_discrepancy_refresh
Monitor your inbox to ensure the issue is resolved by the elimination of the email indicating an error in the rowset. Now you’re ready to get back to analyzing the data.