If PowerPivot, Power Query, and Power View had a baby (don’t ask how) that baby would be called Power BI Desktop Designer. Yesterday the Power BI Desktop Designer was released for general availability, which I promptly downloaded last night at 11:30 PM EST and started playing with. Even as my wife turned out the light and begged me to go to sleep, I persisted! I was too excited. So here’s my first run through (I call it run through because it was late and I didn’t spend a ton of time looking at every little thing).
Here are 3 Power BI best practices to follow
Download Power BI Desktop
Ok first things first. Download the Power BI Desktop Designer so you can start playing!
http://www.microsoft.com/en-us/download/details.aspx?id=45331
Installing it is pretty straight forward so I’ll spare you the details.
Get Data with Power BI Desktop
The first thing I did after installing the Power BI Desktop Designer was click the Get Data button.
This is where we can specify the data sources that we want to pull into our report. As you can see from the couple screenshots below, there’s a lot of choices for data sources similar to Power Query. If you’ve got data in traditional data sources like SQL Server, Oracle, DB2, you can access them here. I just have a boring Access database version of Adventure Works, so I’m going to select Access Database here.
Or if you have other less traditional data sources such as Facebook, Google Analytics, QuickBooks Online, or Salesforce, you’re covered.
Then I selected the tables that I wanted to import into my model and clicked the Load button.
The data is loading! Sweet!
After loading the data is completed, I’m presented with a blank screen where I can then begin designing my reports. There are three different views we can use to interact with our model: The Report view, Data view, and Relationships view. The different design interfaces can be accessed on the left side of the Power BI DD.
Creating Relationships in Power BI Desktop
The first thing I did was click the Manage Relationships button in the Home ribbon. This is where we can create and manage relationships between the tables in our model.
To create a relationship between two tables, you can either click New and manually specify the relationships between tables or click the Autodetect button to let the designer do it for you.
To create a relationship, just click New and select the tables from the drop down lists and highlight the columns to use in the relationship. Pretty easy.
If you want to manage your relationships in a GUI, just click over to the Relationships design interface to take a look at your model.
I want to point out a couple things here. You’ll notice in the relationships diagram that the arrows between the tables have a 1 and a * on the arrows between the tables. This indicates that this is a one-to-many relationship, which can be controlled from the Edit Relationship windows.
To manage a relationship between two tables, you can also just double-click the arrow between the two tables to open the Edit Relationship window.
Edit your Query with Power BI Desktop
If you need to apply some serious changes to your data then click the Edit Query button on the Home ribbon.
This is where things get Power Query-like. We can do a lot of things here just like we did in Power Query: Rename columns, control formatting, pivot or unpivot data, create new columns, modify data types, replace incorrect values, create a header using the first row, etc.
After you’ve made the necessary modifications to your query, go to File and click Close & Load to refresh your data.
Modeling your Data with Power BI Desktop
If you want to model your data, click on the Data view button to view your data like you were in Power Query.
From this view we can make modifications to the format and layout of the data. From here I can control formatting of the fields, hide columns, control sorting, or add calculated columns or measures.
To create a new measure or column, just click the button in the Modeling ribbon, write a little DAX, and you’re done. Numeric type fields in your data will automatically show up as implicit measures for your users, but in certain scenarios where you want to control the measure aggregation instead of leaving it up to your users, create an explicit measure as seen below.
If you have geography data you want to use in a map report, set the Data Category for the field appropriately so Power BI DD will know how to link the field to the map.
If you want to hide a field from the Report View, just right-click the field and select Hide in Report View.
Designing Reports in Power BI Desktop
Now its on to the fun part! Create reports is similar to Power View but there are some differences to the interface. In my opinion, Power View is much more intuitive than the Reports view in Power BI DD, but maybe it just appears that way since I’m not used to it. But still, it was pretty easy compared to designing a report in SSRS or Performance Point.
Check out these 5 Power BI report design tips
I created the new Funnel report type and modified the report to sort by Total Reseller Sales, the calculated measure I created earlier.
I also created a Map report to test out the Data Category setting.
When I was done creating the report I clicked the Publish button to deploy the report to the Pragmatic Works Power BI.
Great success!
And there you have it. My first dashboard designed using the new Power BI Desktop Designer.
I did notice a couple little bugs with controlling the legend position in the map and some other formatting issues that I’m sure will get worked out through the upcoming frequent releases.
Resources
Once again, here’s the link to install Power BI Desktop Designer:
http://www.microsoft.com/en-us/download/details.aspx?id=45331
Use the following link below to see what’s new in the latest version of Power BI Desktop Designer and to get help on working with the tool.
There’s also a helpful guide and video to working with Power BI Desktop Designer that you can watch here:
https://support.powerbi.com/knowledgebase/articles/471664-getting-started-with-power-bi-desktop
Feedback?
Let me here your thoughts: What is everyone else saying about the Power BI Desktop? If you’ve had a chance to take it out for a spin, let me know!
Power BI Desktop Dashboard Examples
Here’s a dashboard I put together using some movie data I found online. There’s lots of formatting and color preferences to get used to in the new design interface.
Let me know what you think! I’d love to hear your feedback on the new Power BI Desktop app!
*Update: James Phillips, a VP at Microsoft, has corrected me in the comments on the name of Power BI Desktop. After GA, the tool is no longer called Power BI Desktop Designer. From henceforth it shall be known as Power BI Desktop. Now please ignore all the references to “Desktop Designer”. 🙂
**Update: As I continue to play with the Power BI Designer, I’ll post any new dashboards I create at the bottom here while learning the tool
thanks for spending time with Power BI Desktop! Really glad you liked it. As you note, it’s only going to get better and better with our (at least) monthly update cadence. The fun is just beginning!! By the way — it’s just Power BI Desktop (versus Desktop Designer)… We dropped the Designer name when we GA’d…. thanks!
Ok thanks for the clarification James. I’m definitely a fan of the tool and can’t wait to see all the enhancements!
Also, as a note, I noticed a couple quirky things with the map formatting. I’m sure your team is aware, but just wanted to point it out.
Hi Dustin, Great post! thank you! As I was trying to play with the tool, I had hardtime getting data plotted on the maps. I have a column which I setup as ‘State’ in the hope that it will get plotted but it did not. It works just fine on my other computer. This makes me believe that there might be some requirements for it to work but I could not figure that out yet. Do you have any reccomendations? Thank you. -Rashmi
Hi, Rashmi, thanks for reading. I’m glad you enjoyed the post.
For working with geographical data, I believe the only requirement is to set the data category as shown here: https://support.powerbi.com/knowledgebase/articles/594282-data-categorization-in-power-bi-desktop
But the issue could also be related to the data and Power BI having trouble mapping the data to a “state”. I would look at the data to make sure the data is clean and contain correct values for mapping to “state”. I hope this helps.
Hi James, how to you link Logged in User Viewing the Report to the “Data Set” (Filtering to only data that the user allow to see) ?
Hi, unfortunately at this point row level security is not supported by Power BI. The best way to implement row level security would be to use a live connection to SSAS using the Power BI Personal Gateway after you’ve integrated your Active Directory with Azure. This would allow your users to access Power BI with the same accounts used to access on-prem data sources. You can read more about that here: https://azure.microsoft.com/en-us/documentation/articles/active-directory-aadconnect/
can you please give an idea about the scope of power BI in future . how it differ from other visualization tools.
Power BI is growing and changing so quickly its hard to keep a handle on all that Power BI will be even in the near future. If you’d like to see the features being considered for Power BI vNext then take a look at this: https://ideas.powerbi.com/forums/282523-bi-in-sql-vnext. You can also view the public Cloud Platform roadmaps to see what’s coming up: https://www.microsoft.com/en-us/server-cloud/roadmap/recently-available.aspx.