It’s very rare to have the opportunity to learn DAX from the world’s leading expert but we will have that opportunity in Portland on June 19-21! The one-and-only Marco Russo is coming to Portland to teach The Italians’ Mastering DAX course. No one knows DAX like Marco and his ability to teach others about simple, advanced and uniquely insightful concepts is truly remarkable. DAX is simple but it’s not always easy. You can create some amazing calculations and analytical expressions with DAX but many advanced concepts allude most users without help learning some challenging concepts. If you need to learn DAX to use in Power BI, SSAS Tabular or Excel Power Pivot – or to take your DAX Skills to the next level, do not miss this training event.
While in town, Marco will also be the keynote speaker at the 2019 Pacific Northwest Power BI Symposium on June 18th. Last year’s event was a big success. We enjoyed hosting great speakers and a large number of local attendees last year and this event will be just as great.
Let’s step through a few very simple things that every Power BI new user needs to know. Power BI is a great tool and it’s fairly easy to get started but there are a few gotchas that everybody experiences. It’s easy to get frustrated with little minor things until you master a few simple features in the designer. I’m just going to walk you through just a few of these items. It doesn’t matter how simple or how complex the project or your data model is, or what your data looks like. What I’m going to show you are some things that everyone experiences.
The first thing is that Power BI like most business intelligence tools really exists for the purpose of grouping and aggregating data. Now, that’s not to say that we can’t use it for detail-level reporting – and we can work with a lot of data but by default, it wants to group and it wants to aggregate. Sometimes that’s not what we want it to do.
I have a table here that I’ve just created, and this is just to simplify things but I’m going to work with a single table. You would experience the same thing if you are working with multiple tables in a data model. As you can see, I have a name column with the name of some of my my favorite cartoon characters. I have a location and I have a numeric column that I want to report on. We’re going to call that my measure or my implicit measure column. it’s just a column of numbers and just keep in mind that we’ve got three records for Fred two records for Barney two for Wilma etc and notice that those are in combination with different locations now I’m going to switch back to a blank report page and I would like to see the number of shells that are sold to a customer at a location and so I’m going to drag and drop the name column on to my report canvas and that’s going to create this table of names. The first thing that I noticed is the text is very small so I want to bump up the text size. I’m looking up here… looking at all of my ribbons for a place to bump up the text and I can’t figure out how to do that. The first thing is that you need to make sure that you have this visual selected. This is a table visual – there you can see that that’s a table and I can move that around and it can get a little confusing because when the table is already selected, and I go to grab it, that it doesn’t always move.
If you click off of the table and then hover over it, and grab it with the left mouse button; then I can move it around but that might seem to be a little inconsistent. I can also resize it but this white table over the white background… it’s hard to see where that table is. I want to bump up the text so I’m going to go to the paint roller. You can see that this is the format option. It’s simple – once you’ve done it a few times. With this table visual selected, when I choose the paint roller, I get to the formatting options. I’m going to go down to “Grid” and here you can see that I can bump up the text size. So, I can just spin that up to let’s say 14 points. That’s a whole lot easier to read. Because this is a unique combination or unique values in that column, I’ve only seeing four of them right now. I’m going to add location. Now, I can drag and drop into the visual.
With the visual selected, if I’m looking at the field list, I can drag and drop into what we call the “field wells”. This is the field list (on the right of the panel)… these are the field wells for the selected visual (on the left of the panel). Now you’re seeing the unique combination of name and location. I’ll drag Shells field into the field wells and notice that for Fred, I only see two rows. If I’m looking at the rows for Fred, how do I know if these are detail rows or if they’re being rolled-up? Now, we go back and we look at the table and we can figure that out pretty easily. If I’m just coming into this data for the first time, I may not be able to tell how how many rows or records there are in the underlying tables behind this visual. How can I figure that out so that I can validate this data and make sure that my data is accurate? Well, the the best thing that you can do is get a row count. There are a couple different ways to get a row count. If I were just to drag and drop one of these fields that I’ve already used into the visual, I can use it to count rows. I drop this down and I can say “let’s count that”. I don’t want to count it distinctly… I just want to count it and that gives me a row count because it is the count of the number of occurrences of location. Here you can see that this row in the table visual actually represents two records in the underlying table and so that kind of solves this puzzle for me by letting me know that 104.08 is actually the sum of the shells for two underlying records where the name is Fred and the location is Bedrock. There are two records that represent that one row in the table visual. Okay… so that that solves mystery number one.
Now another thing that I can do is drag and drop location onto the report canvas. Then I can switch this to Count and that just gives me an overall count for the entire report. I’ll switch that to a card visual whitch makes that a little bit bigger, so now I can reconcile the number of rows that I see here (which is six) against the number of records in my table (which is 8), knowing that that there’s at least one row here that consists of two or more underlying records in the table. That’s an easy way to resolve that.
Alright, item number two is how do I know where these visuals are on the page? I see that I’ve resized this visual and there’s whitespace all over the place and that’s not very intuitive. What I like to do in new reports is to choose the background of the report canvas, go to the paint roller (make sure that we’re looking at page information) so you should see that I have this property group called “Page Background”. Again, that’s because I just clicked on the background of my page. I’m going to go to the page background and I’m going to set the background color to a light shade of gray. I notice that it doesn’t change and that’s because by default the background of a page has a 100% transparency. why? I don’t know but if I change that, you can see that it is now a very light shade of gray. You see that my table also is transparent so I’m going to go to the paint roller and I’m going to turn the background on which does show me a slightly different background color. The default background is going to be white. I’m just going to double check that it is white. I’ll set the background transparency to 0%. I prefer to see solid colored visuals sitting on a light grey background because now I can see where things are and it’s much easier for me to differentiate where those visuals start and stop. That goes a long ways toward helping my users understand what they’re seeing on this report.
What if I didn’t want to roll-up the name and location to a single row on this table? How can I see my detail records regardless of whether the name and location are the same or not? The fact of the matter is that Power BI is always going to group on any fields that have the same value – so you have to have a unique column. You have to have a unique value that you can group on and that is the purpose of this Unique ID column. Before I just show you that I can use that Unique ID to show detail rows, I want to show you something that happens with any numeric column by default in Power BI. Numeric columns are always automatically set to summarize. That’s what this little Sigma icon is just to the left of this field. When you see that. We do want the shells column to roll-up and summed but not the Unique ID column; we want to be able to group on this Unique ID and use it to make sure that we get our detail rows. I need to come over here to the modeling tab and I’m going to drop down the “Default Summarization” list and set this to “Do not summarize”. That gets rid of that little Sigma icon and now if I have my table selected and then when I drag and drop that field… (oh, look at that it’s going to give me a count by default so it’s even overriding my my summarization). I have to tell it not to summarize right here in the field list. I’m actually getting one row in the table visual for every record in the underlying table and that’s exactly what I wanted. Unfortunately, there’s no way to to hide or get rid of this unique ID column. If I didn’t want, I can just make that column wide and I can resize the table and get rid of that the header text.
Hopefully, this helps dispel some some very simple quandaries that a lot of new users struggle with just a little bit. Best of luck as you’re getting started with Power BI!
Our little “Data Geeks” Meetup group in Vancouver, Washington is growing with a line-up of heavy-hitters and bigger crowds.
This month, on April 16th, we are honored to welcome Reid Havens, Microsoft MVP and Business Intelligence superstar from the Seattle area. Reid will be presenting “Demystifying Chart Types & Report Design Principles”. Reid is the Founder of Havens Consulting Inc. He has a formal background in technology and organizational management, having obtained separate Master’s Degrees in Organizational Development & Business Analytics. He has experience working with many of the Fortune 50, 100, & 500 companies. Additionally he guest lectures at the University of Washington and other universities, teaching Business Intelligence, Reporting, & Data Visualization.
Please RSVP on the user group Meetup page for the event. Thanks to Lexicon Solutions for sponsoring this month’s meeting. Please visit their site if you would like to upgrade your career or are seeking technical talent.
We’re seeing considerable cross-pollination of complimentary user groups in the greater Portland area which gives professionals in the region several quality, free learning and networking opportunities every month. These groups include the Oregon SQL PASS Chapter, which meets downtown Portland on the second Wednesday evening and the Portland Power BI User Group, which meets on Beaverton on the 4th Wednesday evening of each month. With the Vancouver Data Geeks meeting on the 3rd Wednesday evening, if you attend these data-centric user groups three out of four Wednesday evenings each month, you will be smarter than you can imagine and should have immense career opportunities!
In February, we had international trainer and well-known conference speaker, Peter Meyers share his Power BI insights to a crowd so large that we had to find a larger venue. Indeed, our Southwest Washington technical community is growing. Little did we know that if you search the Meetup site for ‘Data Geeks’ in Vancouver, you might find the “Vancouver Data Geeks” group in that other Vancouver way up north of the 48th parallel. We didn’t plan that nor did they but it has a been a minor point of confusion. We may be the smaller of the Vancouvers but we are making a name for ourselves, even in the shadow of Portland, Oregon; our big sister city to the south, just over the river.
Thank you all for participating in our growing community and we look forward to seeing you on April 16th for Reid’s session at the Interject Data Systems Office in Salmon Creek, right off I-5, Exit 7A.