PowerBI.tips

Tag: Mapping

  • Create Custom Mapbox Styles

    Create Custom Mapbox Styles

    If you’ve played around with MapBox in Power BI – you’ll know that it has loads of great features to create really rich and beautiful maps, including some great ‘out of the box’ map styles (i.e. base maps).   However, you might not be aware that it also gives you the ability to design custom Mapbox styles with your own spatial layers. I discovered this feature on a recent project where my client wanted to include Victoria’s Catchment Management Authority (or CMA) boundaries on their base-map to provide greater context to help interpret their data. Up until this point, the only option I knew of was to purchase an expensive ArcGIS Online licence to create custom map styles. So, you can imagine just how excited I was when I discovered that I could also do this in MapBox – for FREE!!!

    Reasons to Design Your Own Style

    There are plenty of reasons to design your own map styles, including:  

    1. Provide Context: I touched on this above, but the ability to add your own layers to the backgrounds on your maps goes a long way to providing additional context to help your report users understand and interpret their data.  
    2. Simplify: Sometimes you need to peel back the complexity to help your data stand out. Customizing base maps allows you to remove unnecessary elements which may distract your users from the data, and dynamically add back complexity at different zoom intervals (watch Step 4 of the Video to see how this works!) 
    3. Consistency, Branding, and Themes:  Customizing the style allows you to design your maps with a similar ‘look and feel’ to other visuals in your report, as well as corporate branding and color themes. For the passionate Power BI designers out there – you’ll LOVE this feature (we’re always on the hunt for new formatting options in Power BI!) 

    I’m not going to lie, there are a couple of steps involved in designing your own custom styles for MapBox in Power BI, but believe me – it’s totally worth it! I walk through the key steps in this short video, which include:

    1. Create a MapBox map in Power BI (check out the video on MapBox 101 if you’re new to MapBox, or need a refresher).  
    2. Upload your custom tilesets into MapBox Studio
    3. Duplicate an existing MapBox style from gallery
    4. Customise your own map style
    5. Add your custom style into your MapBox visual in Power BI!

    First, you can leverage the base choropleth map you created in the Mapbox 101 tutorial to get a starting point. The following steps describe how to enhance the style of the map to make it pop even more!

    In this tutorial we want to add a boundry onto the base map. This will help us outline the specific areas around the rivers that we added as a layer in our Drill Down tutorial. A shape file was created and the zip loaded into Mapbox as a new tileset.

    The next step is to duplicate an existing map style.
    1) Select New style
    2) Choose a template
    3) Click Customize Basic

    Rename your Mapbox style (upper left side of page)

    Create your own custom layers and features to the map. Here are the changes made to this example.

    Click Add new layer

    Search for tileset. (Our example uses the new boundry layer we created.)
    1) Search for the tileset (ex. “CMA”)
    2) Select tileset
    This will add the tileset to the base style we selected.
    3) Click Type
    4) Select Line to change the setting

    1. Select Style
    2. Choose Color
    3. Adjust width by clicking on the Width field
    4. Style the color based on a zoom range. Select Style across zoom range to adjust the slider.
    1. Add new Zoom Range by Clicking on the Zoom Range bar
    2. Select second zoom range field (Zoom 22)
    3. Adjust zoom range to different value (10)
    4. Change the color (Blue)
    5. Scroll down and Click Done

    Great! Now as you zoom in and out of the map you can see the boundaries change color from pink to a blue. You can imagine how powerful this visual change can be in directing the attention in different map visuals.

    The final step is to take our new style and apply it to the Mapbox visual!

    1. Select the ellipses of our style
    2. Copy the Style URL
    3. Jump back into the Power BI Desktop. Under Viz Setting and the Map Style Property, Select Custom.
    4. Paste the copied URL from step 2 into the Style URL

    Final Custom Mapbox Style

    The case study I present is a Power BI report with three maps to help design our future cities to account for population growth, while also considering our environmental and social values (created using ‘synthetic’ datasets for demonstration purposes only). I modify the default ‘Outdoor’ MapBox style to include my own custom tileset, and show how to create dynamic color gradients based on different zoom intervals.

    If you’re keen to learn how to create really cool customized maps for your Power BI reports, then make sure to check out the other blogs in this 4-part MapBox series, where we provide an introductory MapBox 101, explore how to create 3D maps, and how to design drill down maps!

    And if you want to learn how you can create really beautiful and engaging Power BI reports, get in touch with one of the DiscoverEI team to register for our brand-new online training courses:

    • Power BI Designer Masterclass
    • Power BI for the Environmental Industry
    • Power BI for the Water Industry

    Take a look here for more details (https://www.discoverei.com/training)

    If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials.  Subscribe to our YouTube Channel.  Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

    Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

    Check out the new Merch!

    Hasta La Vista Data
    Go Ahead Make My Data
    PBIX Hat


  • Drill Down Maps in Power BI with MapBox

    Drill Down Maps in Power BI with MapBox

    If you’ve played around with MapBox in Power BI – you’ll know that it has loads of great features to create really rich and beautiful maps! One feature I love is the ability to ‘drill down’ to dynamically uncover different layers of your spatial data hierarchy, just in the same way that you can drill down on a standard column chart or matrix in Power BI.

    Why Drill Down on a Map?

    1. See the big picture: Using drill down, we can visualize the data set at the ‘big picture’ scale, and then explore finer details for areas of interest (just think of the classic spatial hierarchy of Country -> State -> City -> Suburb)
    2. Save on real estate: We can also display multiple datasets at different levels in the same map – which goes a long way to conserving your precious report page real estate by reducing the number of visuals in your report!  
    3. Performance: Following on from above, less visuals almost always equates to better report performance! And this is especially true when using MapBox, which is at the slower end of the Power BI performance scale at the best of times – so using drill down to reduce the number of maps combined with filtering your datasets goes a long way to speeding up your reports!

    Create a Drill Down Map in MapBox

    Setting up a drill down map in MapBox is pretty simple once you get the hang of configuring Choropleth maps, and in this video blog I walk through the key steps:

    1. Create a MapBox ‘Choropleth’ map (check out the video on MapBox 101 if you’re new to MapBox, or need a refresher on creating Choropleth maps).
    2. Add hierarchy layers to the  ‘location’ MapBox field well
    3. Enter the vector layer properties from the MapBox Studio for the additional map levels

    In my example, I create a drill down map of priority Catchments and Rivers across Victoria using ‘synthetic’ data, which I created to visualize the nutrient concentration over time (for demonstration purposes only!).

    Instructions

    ps://powerbi.tips/2020/04/mapbox-101-for-power-bi/

    Locate the RIVERNAME field and Drag & Drop it into the second position in the Location field well under BASIN_NO.

    Now navigate to the Format area and select the Choropleth section again. Increase the Number of levels to 2, and Select the Custom Tileset in the Data Level 2 section.

    After you set the above you need to jump out of the Power BI Desktop and head over to the mapbox studio. Here you would take the second layer of the map that you created by choosing another tileset. (For details on how to do that watch the Mapbox 101 tutorial.)

    1. Copy the Tileset ID from Mapbox and insert that value into the PBI Desktop Vector Tile Url Level 2 property still under the Choropleth section.
    2. Copy the layer name from Mapbox and Paste into the Source Layer Name Level 2 property.
    3. Copy the field you want from the attribute list in Mapbox and Paste into the Vector Property Level 2.

    Mapbox Drill Down Results

    We now have the ability to select our map area and drill down into the second layer. In our example, the rivers are now shown under each catchment.

    If you’re keen to learn how to create really cool customized maps for your Power BI reports, then make sure to check out the other blogs in this 4-part MapBox series, where we provide an introductory MapBox 101, explore how to create 3D maps, and how to design your own styles!

    And if you want to learn how you can create really beautiful and engaging Power BI reports, get in touch with one of the DiscoverEI team to register for our brand-new online training courses:

    • Power BI Designer Masterclass
    • Power BI for the Environmental Industry
    • Power BI for the Water Industry

    Take a look here for more details (https://www.discoverei.com/training)

    If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials.  Subscribe to our YouTube Channel.  Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

    Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

    Check out the new Merch!

    Hasta La Vista Data
    Go Ahead Make My Data
    PBIX Hat


  • Create 3D Maps in Power BI with MapBox

    Create 3D Maps in Power BI with MapBox

    If you’ve played around with MapBox in Power BI – you’ll know that it has loads of great features to create really rich and beautiful maps! And one of my favorite ways to get a new client excited about Power BI is to create an awesome 3D map of their city. Now, I know that 3D features get a bit of a bad wrap in traditional data viz circles (don’t even think about creating a 3D pie chart!!!), so why then are 3D maps different?

    Why use 3D maps?

    1. Greater context: We see our world in 3D, so when we transform a flat 2D map of our cities or mountains into a 3D map, it immediately creates greater connection and a sense of perspective.
    2. Another dimension: 3D maps don’t just have to be based on ‘relative’ heights, you can use the 3D effect to add a new dimension to your visualisation, such as population per town, to communicate key insights at a glance.  
    3. They just look really fancy: Sometimes half our battle as data analysts revolves around getting people within our (or our clients) organisations engaged and connected to their data. Creating 3D maps can go a long way to get that ‘wow’ factor which gets people excited and interested in the data!  

    Get started with MapBox

    There are a couple of tricks to getting the 3D features working in MapBox, so in this video blog I walk through the key steps to help you create your own 3D maps using MapBox:

    1. Create a MapBox ‘Choropleth’ map (check out the video on MapBox 101 if you’re new to MapBox, or need a refresher on creating Choropleth maps).
    2. Add height in the MapBox field pane
    3. Adjust Extrusion Height, Pitch and Properties

    In my example, I create a 3D map of buildings across Melbourne as part of a ‘Green Roof’ assessment tool to help optimize the benefits of blue-green infrastructure across Melbourne. Note that I have anonymized this  data and the calculations so that I can share it with the Power BI community (for demonstration purposes only!).

    Instructions

    ps://powerbi.tips/2020/04/mapbox-101-for-power-bi/

    Create a data set that includes the height of the objects, in this case the height of buildings.

    Drag the Height column from your data set into the Size field well.

    Click on the Format section of the visual and select the Choropleth toggle.

    Scroll till you find the Extrusion Height. You will likely need to Adjust the value to a lower value. In our case, we take it from 500 to 5. The Extrusion Height is a multiplier of the Height value from our data set.

    Now, we can look one option below and we see Extrusion Pitch. We are going to Adjust this from 0 to 30. This will provide an angled view of the map which provides a clearer picture of the dimensions.

    Adjust MapBox Properties

    Under the Viz Settings we want to Select the Map Style and change it to Satellite

    Add Color to the different suburbs. Select the Data Colors header and Click to open up the options. Now you can Set the colors of the different areas to visually separate them.

    If you’re keen to learn how to create really cool customized maps for your Power BI reports, then make sure to check out the other blogs in this 4-part MapBox series, where we provide an introductory MapBox 101, explore how to create multi-layer drill down maps, and how to design your own styles!

    And if you want to learn how you can create really beautiful and engaging Power BI reports, get in touch with one of the DiscoverEI team to register for our brand-new online training courses:

    • Power BI Designer Masterclass
    • Power BI for the Environmental Industry
    • Power BI for the Water Industry

    Take a look here for more details (https://www.discoverei.com/training)

    If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials.  Subscribe to our YouTube Channel.  Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

    Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

    Check out the new Merch!

    Hasta La Vista Data
    Go Ahead Make My Data
    PBIX Hat


  • MapBox 101 for Power BI

    MapBox 101 for Power BI

    Maps are a fantastic way to communicate spatial data – and lucky for us Power BI has loads of awesome mapping visuals to choose from. However, the MapBox Custom Visual is definitely my favorite!

    This Tutorial will walk you through the basics of getting started with MapBox.

    Why do I love MapBox?

    1. It’s versatile: You can create pretty much any type of map you’d like, whether it be Circle, Heatmaps, Clusters, Chloropleths, Rasters…there’s plenty of options to chose from all in the one visual.
    2. Ability to customise: Using the MapBox Studio, you can design and host your own spatial datasets, create custom base maps, and visualise them in Power BI.
    3. But best of all… It’s free: Unlike the ArcGIS custom visual, where to unlock most of the cool features you need a paid ArcGIS online licence, you have access to all of MapBox’s amazing features with their free account.

    MapBox is Feature Rich

    With so many features available in MapBox, I know that I was pretty overwhelmed when I was first getting started. So, in this video blog I walk through the key steps to help you get started on your MapBox journey, including how to:

    1. Add the MapBox custom visual into Power BI
    2. Create a MapBox account and access token
    3. Create a simple circle map
    4. Upload custom ‘tilesets’ to the MapBox Studio
    5. Create a custom ‘Chloropleth’ map (just a fancy name for an area map…)

    In my example, I create both a circle and chloropleth map using ‘synthetic data’ which I created to visualise community satisfaction scores for priority waterways across Melbourne (my hometown!).

    Instructions

    Click on the ellipsis in the Visualizations window and Select the option Import from AppSource in the drop down menu.

    Enter in the search window the words mapbox. Click on the Search Glass icon. Then Click on the Add button next to the Mapbox Visual.

    Visit the website mapbox.com and Sign In. If you don’t already have an account you will need to create one. An account can be created here.

    Once you login you can see the public access token on the main login screen. To copy the token to your clip board, Click on the Clipboard in the Default public token window toward the bottom of the screen.

    Take the copied token and place it in the mapbox visual inside the Properties pane, under the Viz Settings for the field labeled Access Token.

    Now, add fields from your data model into the map visual to create a map.

    Return to your account within mapbox.com. Click on your account icon, then Click on the Studio option in the drop down menu.

    Click on the option on the main header bar labeled Tilesets.

    Click on the button labeled New Tileset.

    Next, upload your shape file into mapbox by clicking Select a file.

    You will need three pieces of data to add the Chloropleth map.

    • You need the Vector Tile Url Level 1 or the Tileset ID from mapbox.com. Obtain this by clicking on one of the tilesets in mapbox.com and copying the Tileset ID by clicking on the clipboard icon in the Tileset ID window. Paste this value into the field labeled Vector Tile Url Level 1. Note: The final URL needs to contain the mapbox:// beginning URL. Thus, your final URL should look similar to the following mapbox://mapbox.mapbox-terrain-v2
    • You will need the layer you will be visualizing. This is found under the Tileset Layer Details. Copy the name of the Layer you will use in your mapbox visual. Paste this property into the Source Layer Name Level 1 input box.
    • Finally, add the data layer of the Tileset into the Vector Property Level 1. This must be a column of data that resides inside your Power BI data model. In the video example our column name was POSTCODE. This column was also added to the Location field settings of the mapbox visual.

    With these settings in place we can now see a Cloropleth map of our data. Continue styling the visual to suit your needs.

    If you’re keen to learn how to create really cool customized maps for your Power BI reports, then make sure to check out the other video blogs in this 4-part MapBox series. Additional videos will cover how to create 3-D maps, multi-layer drill downs, and design your own styles!

    And if you want to learn how you can create really beautiful and engaging Power BI reports, get in touch with one of the DiscoverEI team to register for our brand-new online training courses:

    Take a look here for more details (https://www.discoverei.com/training)

    If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials.  Subscribe to our YouTube Channel.  Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

    Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

    Check out the new Merch!

    Hasta La Vista Data
    Go Ahead Make My Data
    PBIX Hat


  • Temple of DAX – 3D

    Temple of DAX – 3D

    To celebrate joining the Power BI Cat team next week, I thought I would update a DAX game I built last November (2018) which was a DAX-based maze game in Power BI that allowed you to navigate a character through a series of user-created maps in a top-down view.

    The details for that game are in this blog posting. http://radacad.com/fun-with-dax-a-maze-ing-dax.

    The update I wanted to make was to see what is involved in creating a 3D, first-person version of the same maze.  The following is how I got on.

    The PBIX File for the Temple of DAX – 3D can be downloaded using this link and here is a link to the publish to web version.

     

    Like the 2D version, the report imports a CSV file which it uses to generate the map.  I pretty much used exactly the same code to import the CSV file and pivot the coordinates to a format for suitable for processing in DAX.

    All you need to create/edit your own custom maps is to open and edit a CSV file using your preferred editor and place an X character where you would like a wall to be and an I character for the insight (or exit).  Excel is great for this particular task as you can size and align the columns nicely to easily see where walls will be.

    The following image shows the data1.csv file opened in MS Excel, with all columns set to the same width of 15-pixels.  Once modifications are complete, save the file as CSV (not xlsx) in the folder the PBIX file uses to import from.

    Once again, I’d like to thank Margarida Prozil for providing a custom control to manage the navigation.  This is an updated version of the D-PAD called a 3D-Pad.  You can grab a copy from her GitHub repo if you are keen to work with this control.

    https://github.com/mprozil/dPad-3D/tree/master/dist

    The control has four arrows.  The Up/Down arrows move you back and forward through the map, while the left/right arrows spin you 45 degrees.  This is different behaviour to the D-Pad in the 2D version of the maze.

    I’d also like to thank Mike Carlo at PowerBI.Tips for providing the wonderful background image and the overall visual design of the game.  It was Mikes idea for the evil laugh on the intro page.  This started to drive me a bit nutty as it would play everytime I saved the file.

    The Game Logic

    Like the 2D game, this version uses SVG as the method to draw the graphics to the screen.  Each time an arrow is clicked, a new view needs to be generated based on the interaction, so a calculated measure takes into account the new position or perspective and generates a fresh SVG to be displayed using the Image control by CloudScope.

    The SVG image displayed in the Image Control is a nested set of mini SVG polygons that draw the outline of various shapes into an outer SVG file.  The performance is pretty good and I have still yet to hit any limitations on text size to store the final SVG set.

    The 3D world

    The first challenge was to figure out how to convert coordinates in a table in the data model into an image that looks like you are walking through a maze.

    The map I use in the uploaded PBIX file is 26 steps and 36 steps long, so it isn’t big.  If a step has been designated as a wall, it effectively has 4 walls (panels) around its perimeter, and when a series of X values in the CSV run next to each other, a longer wall effect is created.

    Each of the panels for a step in the board has 5 points.  I initially created these panels as 100 x 100 walls, but I found it added the extra point at the top to help me work out which way was up when I was first playing with the 3D to 2D projection code.

    The Calculated table called 3D Worlds takes the basic X/Y coordinates from the CSV file and generates a 3D world of X/Y/Z points for every panel on every all in the maze.

    The image below shows all X/Y/Z coordinates for 1 step in the CSV map.  The first column shows there are four panels.  Think of these as like north, east, south and a west facing wall.  Each wall as five points.  These five points define each corner of the wall in terms of a 3D world and the X, Y and Z columns for each row specify exactly where each point should be in a three-dimensional plane.

    3D to 2D projection

    The next challenge is to take the 3D coordinates and convert this information into an image that represents what you should see based on your location in the map, along with the direction you are looking.  There was no way I was going to figure the maths for this out from scratch, and after a few internet searches and a bit of reading, I found this article to have all the information I needed.

    https://en.wikipedia.org/wiki/3D_projection

    The key information is in the section on Perspective Projection, including the algorithm I ported into the DAX measure in the game.

    The basic principle is to define a 2D plane (or screen) called a viewport that sits a specific distance from the eyeball. Then an imaginary line is calculated between the eyeball and every X/Y/Z point from the 3D World table.  If any of these lines pass through the viewport, they can be plotted onto the viewport at a specific 2D x/Y coordinate.

    The code I used from the Wikipedia article is the following:

    The Main Map calculated measure contains all the DAX code to convert the 3D data points to 2D coordinates using SVG.

    The section of code in the Map Map calculation that matches first the algorithm from the Wikipedia article is here :

    I’m not going to pretend to understand this any more than it reminds me of math classes from when I was 15, working out SIN, COSINE equations.  I swore then I would never use them for real, so I guess I owe my old maths teacher an apology.

    The good news is I didn’t need to understand the code in the end.  Once I’d added it to the DAX calculated measure and started generating SVG polygons, it’s quickly looked the way I wanted.

    I probably spent less than 2 hours getting this aspect of the game to work.  There was a little fine-tuning while I worked out what the various elements were, but this was much easier than I anticipated.

    A nested set of CONCATENATEX functions in the Main Map calculated column, loop through every object defined in the 3D World table and converts points to 2D versions.  A series of filters are applied to stop plotting any graphics that fall outside the 2D viewport, such as walls that are behind you (taking into account the direction you are looking).

    The objects are drawn from furthest to nearest to make sure far away objects do not appear if they are covered by a nearer object.  This also means portions of further away objects will appear as expected if they can be partially seen.

    Other filters are applied to stop panels of walls being plotted when they simply cannot be seen.

    Mini Map

    A separate Mini Map calculated measure generates a non-3D version of the map and gets added to a separate Image Viewer custom control to show your position in the map.  A small red triangle shows your position and direction and a small white square shows the exit.  In this case, it’s inside the letter E.

    3D Dpad

    Margarida Prozil supplied me with an updated 3D control that sits on the top of three separate columns in the database.  X and Y (rows and columns) control the square you currently occupy, while the third column is V (for view perspective).  X and Y represent how many squares wide, or long the map is.  V represents the angle in steps of 45 degrees and there are 8 possible values (0, 45, 90, 135, 180, 225, 270  and 315).  The example map is 26 squares wide, 36 squares long so combined with the 8 views, mean the control can be set to 7,488 possible values.  A custom control can only manage about 30,000 points, so take this into account for larger custom maps.

    Summary

    I’m pretty happy with the 3D effect and can see it can be used with other X/Y/Z based 3D coordinates plotted to a 2D view plane.  When I was debugging this, I had slicers for height on the screen so I could use a slider to create an effect of flying up in the air – and the maze still rendered as expected.  Power BI seems to cope with the workload pretty well, despite not really optimised for this kind of work.  In future versions, I may add more objects and detail to push the engine harder and see where breaking points are.

    There probably aren’t too many business use-cases in Power BI for this type of report aside from educational.  Feel free to have a look through the PBIX file where you may pick up some useful ideas, tips and tricks in the DAX.

    There is a small bug at the start of the game when the bookmark drops you into the maze and you need to click the up arrow a few times to get going.  Once you are on your way, it’s pretty good.  I’ll try to get that resolved in the next few days.

     

     

     

  • Power BI Histogram with Bins – Includes DAX

    Power BI Histogram with Bins – Includes DAX

    Alright to start this Tutorial off right we are going to incorporate the new feature released this spring from Power BI, called publish to web.  Below you can view last weeks tutorial and interact with the data.  Feel free to click around to see how the visualization works (you can click the shaded states or on the state names at the bottom.

    For this tutorial we will build upon the last tutorial, From Wikipedia to Colorful Map.  If you want to follow along in this tutorial click on the link and complete the previous tutorial.

    Materials:

    • Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
    • Mapping PBIX file from last tutorial download Maps Tutorial to get a jump start.

    Picking up where we left off we have data by state with data from the 2010 Census and 2015 Census.

    Data from Region Maps Tutorial
    Data from Region Maps Tutorial

    What we would like to identify is how many states are within a given population range.  Say I wanted to see on the map, or in a table all the states that had 4 million or less in population in 2010.

    To do this we will create bins for our data.  Enter custom data in this format.  For the tutorial on entering custom data into Power BI Desktop check out this tutorial on Manually Enter Data. Click on the Enter Data button on the Home ribbon.  Enter the data as following:

    Enter Bucket Data
    Enter Bucket Data

    Note: Make sure you name the new table Buckets as shown in the image above.

    Click Load to bring the data into the data model.  Notice we now have a new table in the Field column on the right.

    Buckets Table
    Buckets Table

    Next we will create a measure to evaluate the state level data into our newly created buckets.  This will be produced using DAX (Data Analysis Expressions).  DAX is an extremely powerful language which is used in SQL applications and Analysis Services.  More information can be found on DAX here.  Since DAX is so complex we won’t go into a full explanation here.  However, we will have many more topics in the future working on and building DAX equations.

    Click the Ellipsis next to the table labeled US Census. Then click the first item in the list labeled New Measure.

    Note: Ellipsis is the term used for those triple dots found in newer Microsoft applications.

    Example of Ellipsis
    Example of Ellipsis

    A formula bar opens up underneath the ribbons bar.  Here is where we will name and type in the new measure.  The equation we will need to add is the following.

    Bins = CALCULATE(COUNTROWS(FILTER('US Census',and([2010 Census] >= min(Buckets[Min]),[2010 Census] <= MAX(Buckets[Max])))))

    Press Enter to enter the measure into PowerBI.

    Explanation of Equation: All text before the equal sign is the name of the measure.  All the data behind the equal sign is the DAX expression.  Essentially this equation is calculating the number of rows where we have data between the Buckets “Min” value and Buckets “Max” value.  This is the magic that is DAX.  In this simple expression we can compare all our data against our buckets ranges we made earlier.

    Finally our new Bin measure should look like the following.

    Bin Measure Created
    Bin Measure Created

    Now lets modify our visuals to incorporate the new Bins measure.  Click on the existing map on the page.  Remove the % Change item from the Values selection.  Add the Bins Measure to the Values section.  Notice the map changes color.  Next, add the Name field from the table called Buckets into the Legend field.  Our map should look similar to the following:

    Map with Bins Added
    Map with Bins Added

    Next Click on State, 2010 Census, Bins, and Name (from Buckets table) and make a table.  It should look like the following:

    Table of Bins Measure
    Table of Bins Measure

    Lastly, we will build a bar chart using our Bins Measure.  Click on the Stacked Column Chart Visual and add the following items to the corresponding categories:  Axis = Name (from the Buckets table), Legend = Name, and Value = Bins (from US Census table).  This will yield the following visual.

    Bins in Bar Chart
    Bins in Bar Chart

    Click on the Ellipsis of the bar chart and then click Sort By, finally click Bins. This will order the items in descending order by the count of the items found in each bin.

    Now have fun with your new data.  Click on each of the bars in the bar chart and watch your data transform between the table, and the map.

    Selection Big in the Bar Chart
    Selection Big in the Bar Chart

    Here is the final product if you want to engage with the data.

    I have to give credit where credit is due.  Below is the page from Power Pivot Pro that I used to create binning in the tutorial chart.  The binning shown on PowerPivotPro is for Power Pivot but the functionality is the same. Enjoy.

    http://www.powerpivotpro.com/2015/03/creating-a-histogram-with-a-user-defined-number-of-buckets/

    Want to learn more about PowerBI and Using DAX.  Check out this great book from Rob Collie talking the power of DAX.  The book covers topics applicable for both PowerBI and Power Pivot inside excel.  I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.

  • From Wikipedia to a Colorful Map

    From Wikipedia to a Colorful Map

    For this tutorial we are going to get some real data from the web.  One of the easiest sources to acquire information from is Wikipedia.  I will caveat this by saying, it is easy to get data from Wikipedia, but I don’t know if you can always trust the reliability.  That being said, we are going to acquire the U.S. population and growth rate from 2010 to 2015 from the Wikipedia Web page.

    Materials:

    • Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
    • Link to the data from Wikipedia, Here.  ( https://en.wikipedia.org/wiki/List_of_U.S._states_by_population_growth_rate )

    Let’s begin.

    Open up Power BI Desktop.  Click on the Get Data button.  On the left of the Get Data menu click Other then select the first item titled Web.  Click Connect to continue.

    Get Data from Web
    Get Data from Web

    In the From Web window enter in the following web address.  You can copy and paste it from below.

    https://en.wikipedia.org/wiki/List_of_U.S._states_by_population_growth_rate

    Click OK to move to the next menu.  After a bit of thinking the Power BI will present the Navigator window.  This is what Power BI has found at that specific web address.  On the left side of the screen there is a folder.  This is the web page folder location that we loaded earlier.  Power BI then intelligently looks through the website code for tables it can distinguish.  By clicking on each table you can see a preview of the data returned on the right side of the window.

    Try clicking on the various tables such as Document, External links, or Table 0.  For our example lets click on Table 0.  Click on the button at the right hand corner labeled Edit.  We are going to slightly modify this data before we load it to the data model.

    Navigator Window
    Navigator Window

    You’ll notice once we load the data there are some items we’d like to remove.  In row #2 the label is District of Columbia, which technically isn’t a state.  Also further down we see in row #25, the entire U.S. population is shown.  Again, we don’t want these values to show, we only want the 50 states.  To remove this data we will use a text filter to remove any item in the Rank column that has a “–” (which is called an em-dash, see note below for more details on how to select this text character).

    Note: There are two kinds of dashes that your computer uses.  One is called the en-dash(-), the second being the em-dash(–).  It is very hard to distinguish the difference between the two dashes.  The image below shows a better contrast when used in Microsoft Word.

    Em-Dash vs. En-Dash
    Em-Dash vs. En-Dash

    The en-dash is shorter than the Em-dash. The Key for the en-dash is next to the number 0 on your keyboard.  To select the em-dash you need to use a bit of Microsoft trickery.  The Em-dash will be presented when you hold the Alt key and type 0151 on a keypad.  This selects the specific ASCII character for the em-dash.  For more information on selecting the em-dash visit here.

    Click the drown down button in the column labeled Rank.  Select the item labeled Text Filters, and then Click Does Not Contain…

    Text Filter on Rank Column
    Text Filter on Rank Column

    Enter in the em-dash code by using Alt 0151 to enter in the correct dash into the Filter Rows dialog box.  Click OK to proceed.

    Enter EM-Dash in Filter Rows Dialog
    Enter EM-Dash in Filter Rows Dialog

    If we entered the correct em-dash we will now be presented with a cleaned list of U.S. states with only numbered items in the Rank column.

    Next we will clean up the query slightly to make it easier to deal with.  Delete the column labeled Rank, and Change.  Rename the query to something a little more meaning full such as US Census.

    Remove Columns
    Remove Columns & Rename Query

    Note:  You can delete a column by pressing the Remove Columns button on the Home ribbon.  A second method is to right click with your mouse on the column you want to remove and selecting Remove.

    Next we will add our own calculated column which will calculate the 2010 to 2015 percent change.  Click the ribbon labeled Add Column and select the first icon on the far left labeled Add Custom Column.  The Add Custom Column dialog box will open. Enter the name for the new column, then by clicking on the columns in the available columns on the right you can build an equation.  For this example we are using the percent change calculation which is the following:

    Percent Change = [New Value / Old Value ]- 1

    Using the columns we imported from Wikipedia we will have the following equation:

    = [2015 estimate] / [2010 Census] - 1

Update: this formula has now changed to 2016 estimate as time has progressed since this first tutorial was posted.
The new column should have this following formula: = [2016 estimate] / [2010 Census] - 1

    This inserts a new column with the calculated percent change between the 2010 census and the 2015 census.  Click OK to proceed.

    Add Custom Column
    Add Custom Column

    Finally we want to change the type of data in the % Change column so our data model will operate as expected when producing visuals.  Click the Home ribbon, then click the % Change column.  Change the Data Type: from Any to Decimal Number.  This informs the data Model how to treat the data held the % Change column.  We are finished data modeling and now click Close & Apply on the Home ribbon.

    Now we have all our data loaded into the data model ready to build a map.

    Click the Column labeled State and then click % Change.  This yields a map with circles on it.  Change the visual to a filled map by selecting a different visual, the Filled Map icon (circled in red below).  Doing so produces a shaded map of the US, where each state is colored according to the % Change.

    Filled Map Selection
    Filled Map Selection

    Finally lets add some color to the data. Click the visual’s Format properties (the little paint brush in the visuals window). Expand the Data Colors section by clicking on the title Data colors. Diverging is set to off.  Change it to On. Change the Minimum color to Green, the Center color to Yellow, and the Maximum color to Red.

    Colored Map
    Colored Map

    The states with the largest population change are in Red, while all the states with the smallest population change.

    Please share if you liked this tutorial.  Thanks.