Tag: Power BI Embedded

Embedding Paginated Reports

Over the course of time Power BI has come to encompass a wide variety of technologies and tools. One such product that has been integrated into the suite of Power BI is paginated reports. Before Power BI, paginated reports were served up in SQL Server Reporting Services. That product was the major reporting tool used by businesses using Microsoft products. Now, Power BI brings us the best in visualization, analysis and insight. Paginated reports give you pixel perfect outputs that can be easily printed, emailed, and output to Excel. It strikes me as a bit peculiar that over the years while the focus has been to push into Power BI visualizations heavily, almost every client I’ve ever worked with has always asked for outputs of Power BI reports that are best served up in a paginated way.

What is really exciting is now we have an ecosystem in which we can bring all these report types together. You can create “perfect world” reports where we can analyze and export in fashions that meet the needs of a wider audience. Today I want to talk specifically about the release of Paginated reports in external embedded scenarios in the November feature release cycle. The integration of paginated reports has been on a steady cadence of releasing new features. I want to serve up an overview of current state for embedding these report types, and where there is room for improvement. I’m also interested in the community feedback to determine if my experiences or blockers are similar or different. Be sure to comment below.

What you need

First off, in order for you to use paginated reports in Power BI, you will need to likely upgrade you license. It is a widely known request that something should be done about removing this feature from premium pricing. If you want to change that, throw your weight into the request to change that, you can do that via the community ideas section for Power BI to make this feature available to Pro users -> https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35959420-paginated-reports-please-make-it-available-in-pr

Importantly, having Pro license won’t fix the cost for an embedded scenario completely, but one can hope that if it is available to Pro, then the A1 – A3 sku levels would work as well for future embedding.

Lets get back on track! You will need an A4 sku or P1 in order to use the paginated reports. A full walk-through of how you can develop this solution can be found here. The great thing about an A sku is that you can turn it on and turn it off. This means that if you want to kick the tires and showcase the capabilities before purchasing, you still have the ability to do so.

What Works

Aw yea! Look at this beauty, you see that in the embedded sample report? PDF, Excel, Word, PowerPoint! All there, and all exporting the pixel perfect greatness of paginated. I don’t have to assume how much businesses will salivate over this one, because I’ve had all these conversations. With paginated reports in our external facing applications we have the ability to merge all these report types into seamless products for our business users.

You want to test this out for yourself? You can! Check out the playground in embedded and you can see just how these features serve up in all your export dreams.

https://microsoft.github.io/PowerBI-JavaScript/demo/v2-demo/index.html

Here are the current data sources that are supported.

Not surprisingly, all the Azure sources feed us up all the data & security that we would want to use in terms of access. This will support existing data extraction methods used for paginated in other reporting tools. There is great news from a Power BI model perspective as well! If you want to integrate your Analysis and paginated reports to serve off the same model this is really easy to do now because I can connect to AAS or a Power BI Dataset.

What is Needed

There are a few things that you should be aware of that aren’t yet baked into the feature release. This is where I’m wondering how much of a blocker it may be for others as it is for me. There are likely many companies that have hundreds or thousands of paginated reports. The majority of your reports are likely served up via stored procedures with parameters in the SSRS report. These provide data context prior to execution of the procedure thereby limiting the results. This WILL work if you are using Azure SQL DB or Azure Managed instance. It WILL NOT work for on-premises SQL or a Platform as a Service (Paas) solution where you are hosting your own SQL Server in an Azure VM.

Export to Excel – Dump my data!… Personally, I don’t mind that this feature isn’t in the export options. I actually hope it doesn’t get added in the way it works in legacy tools. One challenge I’ve always had is trying to get business users out of using reports as export dumping tools. With the advent of Power BI, and all the ways we can surface up curated data sources, I want to see this type of practice stop. We need to get better at training people where and how to ingest data and make it easier for them. Rather than let them dump out loads of data and creating silo’d processes of their own.

Close

I’m really excited that paginated reports has been added as a supported report type for embedding. Paginated reports will certainly hold a place in my future deployments. Additionally, I would love full support for on-premises data and Paas solutions in order for major migrations to occur of paginated reports into Power BI. For me, I will need to consider the cost of refactoring all those stored procedures if I want to stay on-premises or make a case for moving all of reporting only to Azure SQL.

For more details on the release and links to the announcement you can find it here -> The Details: https://powerbi.microsoft.com/en-us/blog/embed-paginated-reports-in-your-own-application-for-your-customers-preview/

If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.

As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:

November 27, 2019
Power BI Embedded Dashboard

For a while now I have been longing to spend more time learning all the ins and out of Power BI embedded. From the line of work that I do, I can see a number of different development applications where an embedded solution would be perfect. Microsoft has been spending more time developing and feature enhancing the embedded experience. The Guy in a Cube (Adam Saxton) has another great video walking through how to embed a dashboard. Great video, and a really good walk through. Thanks Adam.

Power BI Embedded Dashboards:

If you haven’t already make sure you go check out Adam’s site and YouTube Blog for more great information.

For more great videos about Power BI click the image below:

February 28, 2017
Measures – Year Over Year Percent Change
This tutorial is a variation on the month to month percent change tutorial. This specific exploration in year over year performance was born out of reviewing my google analytics information. The specific analysis question I am trying to answer is, how did this current month of website visitors compare to the same month last year. For example I want to compare the number of visitors for November 2016 to November 2015. Did I have more users this year in this month or last year? What was my percent changed between the two months?

Here is a sample of the analysis:

let’s begin with loading our data and data transformations. Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. Click Advanced Editor on the View ribbon. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
```
let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDBDcQwCETRXnyOFMAYcC1W+m9jV8BhfH1ygJ9zBr/8CvEaz+DYNL7nDAFjnWkTTNsUbIqnLfyWa56BOXOagy2xtMB5Vjs2mPFOYwIkikIsWd6IKb7qxH5o+bBNwIwIk622OCanTd2YXPNUMNnqFwomp0XvDTAPw+Q2uZL7QL+SC1Wv5Dpx/lO+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start of Month" = _t, Sales = _t]),
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Month", type date}, {"Sales", Int64.Type}}),
 #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Start of Month]), type number),
 #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Start of Month]), type number)
in
 #"Inserted Year"
```
While still in the Query Editor rename the query to Data. Then click Close & Apply to complete the data load into the data model.

Load Monthly Data

Next, make four measures. On the Home ribbon click the New Measure button. Enter the following to establish a reference date to the subsequent equations:
```
Date Reference = DATE(2016,12,31)
```
Enter in the following equation to calculate the last year monthly sales amount.
```
LastYear = 
  VAR 
    CurrentDate = [Date Reference]
  RETURN
    CALCULATE( 
     SUM(Data[Sales]), 
     Data[Year] = YEAR(CurrentDate)-1
    )
```
Note: Using the NOW() function calls the current time when the query was last run. Thus, if you refresh your data next month the NOW() function wrapped in a YEAR() will return the current year from the date-time observed by PowerBI.

Following the same process enter the following additional measures. The ThisYear measure calculates the sales for the current month.
```
ThisYear = 
  VAR 
   CurrentDate = [Date Reference] 
  RETURN
   CALCULATE(SUM(Data[Sales]),Data[Year] = YEAR(CurrentDate))
```
Finally, add the calculation for the Year to Year comparison.
```
YoY Percent Change = DIVIDE([ThisYear], [LastYear], 0)-1
```
Since the YoY Percent Change is a real percentage we need to change the formatting to a percent. Click on the YoY Percent Change measure then on the Modeling ribbon click the % symbol in the formatting section of the ribbon.

Change Measure Format

Next, add a Stacked Column Chart with the following columns selected.

Add Stacked Column Chart

OK, we have a chart, but it is kinda awkward looking right now. The x-axis is the month number but we don’t have a month 0. That simply does not make sense. Let’s change some of the chart properties. While having the Stacked Column Chart selected click on the Paint Roller in the Visualizations pane. First, click on the X-Axis and change the Type to Categorical.

Change X-Axis

Then click on the Data Colors and turn on Diverging. Change the Minimum color to Red and the Maximum color to Green. Set the Center to a value of 0.

Change Colors

Click on the Title change it something meaningful, Center the text and increase the font size.

Change Title

Our bar chart looks much better. However, the month numbers do not look quite right. Visually the month indicators would be cleaner if we didn’t have any decimals. Click on the Month field and then on the Modeling ribbon change the Data Type to Whole Number. There will be a warning letting you know that you are changing the Data Type of the Whole number. Click OK to proceed with the change.

Change Month to Whole Number

Another successful percent change tutorial completed. I hope you enjoyed this year over year month comparison example. Make sure you share if you like what you see.
December 5, 2016
Grouping and Improved Date Slicer
In the October update of PowerBI Desktop we were given a number of really useful features, ranging from a new Date Slicer, Grid lines, Grouping, Binning, Top N Filters, and R-powered custom visuals. For the full release on the October 2016 software release you can read more here.

For those of you who have followed my site you already know that I absolutely love the ability to create R-visuals within PowerBI. If you want to learn more you can read the R script tutorials here.

As I’ve been exploring this October release of Power BI Desktop two features have really stood out. First, the ability to use the Date Slicer and second the new feature of Grouping for Bar Charts. In my daily work flow I have struggled in the past with solutions which are now solved.

Welcome to my love & hate relationship with time bound data sets. Inevitability at some point you will encounter a need to manipulate data over time. Common business questions will come in the form of: What was my percent change compared from this month compared to last month? What is my sales performance for this year? Are we up or down compared to the same period last year? While these questions are simple I have found that calculating measures and subsequently building visuals can get very complex in a hurry. Enter the Date Slicer. Let me be clear, the Date Slicer will not solve all your problems, it does present a very useful interface that will let report users quickly navigate through their time delineated data.

What is the Date Slicer? I’m glad you asked. Behold….

Date Slicer

Here are a couple of key items to point out. On the left side of the visual you are given the ability to select the starting date, and ending date.

Start and End Dates

The bottom of the Date Slicer has an adjustable time bar that allows quick time adjustments.

Changed Timeline

On the right side of the visual you can toggle between different date selection modes, Between, Before, After, and List.

Time Parameter Selector

Ok, enough about the Date Slicer, how about the Groupings feature. Lets say you start off with a bar chart that looks similar to the following:

Sample Bar Chart

Well, maybe you’re only interested in Items 9, 10, and 5. Grouping now allows the selection of multiple bars and by right clicking you can Group, Include, or Exclude.

Right Click on Selected Data Bars

Clicking group creates a new column in the Fields area that groups the items together and adds them to the chart legend.

Grouped Items in Bar Chart

Let me tell you this is helpful, especially when your bar chart looks like this:

Ugly Bar Chart

Be honest, you have published a report or two when there were just way to many bars. The different between the largest bar and all the small bars barely tells you any information. This is why grouping is helpful. When you receive data and you need to focus your story to the important pieces then grouping is your friend.

Enough babbling, let’s get to the tutorial.

Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. Click Advanced Editor on the View ribbon. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
```
let
 Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2016/11/Sales-Data-Two-Years.xlsx"), null, true),
 Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Sales_Table,{{"Date", type date}, {"Product", type text}, {"Sales", Int64.Type}})
in
 #"Changed Type"
```
Rename the Query to Sales Data. Once you’ve completed the data load your data should look like the following.

Load Sales Data

On the Home ribbon click Close & Apply to complete the data load.

Close and Apply

Great, we are ready to start adding visuals now. Add a Slicer visual from the Visualizations window and populate the visual with the Date field. By default, the slicer will auto recognize that the field being added is a date and will automatically show the Date Slicer.

Add Date Slicer

Next, add a bar chart visualization. The Date field will be the Axis, and the Sales will be the Value.

Add Bar Chart

Again by default the Date filed will be entered as a Hierarchy field. Click the Drill Down button until you see a monthly view of the date. This should require two clicks, the first drills down to quarters, then second click drills down to month level. After doing this your visual should look like the following:

Drill Down to Month

Add a second bar chart with following fields selected.

Second Bar Chart

Sort the Sales by Product bar chart in descending sale order by clicking the Ellipsis and when the drown down menu appears selecting Sort by Sales.

Descending Sort by Sales

Now that we have built a couple visuals and a Date Slicer, take some time to explore how the Date Slicer interacts with the bar charts. In the example below I modified the starting date to 6/1/205 and the ending date to be 9/30/2015.

Modify Date Slicer Properties

To utilize the grouping feature we will group Items 1,2,3 and 4 together. While holding the control button click Items 1,2,3 and 4 on the Sales by Product bar chart.

Ctrl Click Items

Right click Item 3 an menu will appear, select Group. Notice once you do this a new Product (Group) field appears in the field menu and the Product (Group) is automatically added to the legend of the bar chart visual.

Grouping in Bar Chart

To edit the grouping you can click on the Field labeled Product (groups) and select Edit Groups. Doing this reveals the grouping dialog box.

Grouping Dialog Box

You can rename the created group by double clicking the name Item 1 & Item 2 & Item 3 & Item 4.

Rename Grouping

Change the name of the grouping to be titled Special Items. Click OK to close the Groups dialog box. Notice how the bar chart updates the legend values to renamed grouping.

Rename Group

Well, that wraps up this tutorial. I hope you enjoyed it. Below is a live demo of what we built today.

Make sure you share the content if you liked this tutorial.
November 16, 2016
Measures – Intelligent Card Visual – Using DAX
As I have been exploring PowerBI and building dashboards I have noticed that often the visuals can obscure your data. As you click on different visuals there is a need to highlight different pieces of data. Take for example the following dashboard:

Sample Visual Example

Notice the different car types in the bar chart. As you click on each vehicle type, Diesel, Hatchback, etc.. you expect the data to change accordingly. In some cases it is helpful to present a card visual to show the user what you selected and any relevant data points you want to highlight. For example if I select the Diesel vehicle type I may want to know the average sales amount, total sales in dollars, or number of units sold. This is where we can build specific measures that will intelligently highlight selected data within your PowerBI visual.

Here is a sample of what we will be building today:

lets begin with starting with some data. In honor of your news feed being bombarded with Pokemon Go articles lets enter some data on Pokemon characters.

We will enter our data manually. For a full tutorial on manually entering in data visit here.

Click the Enter Data button on the Home ribbon and enter the following information into the displayed table.

Pokemon XP

Pikachu 1200

Weedle 650

Pidgey 800

Golbat 300

Rename the table to Characters. Once you are finished entering in the data it should look like the following:

Create Table of Characters

Click Load to continue.

Start to examine your data by building a table visual.

Table Visual

Next add a Bar chart.

Bar Chart

Note: I added the XP column twice. Once to the Value attribute and to the Color Saturation. This enhances the look of your visual by coloring the bars with a gradient. The largest bar will have the darkest color, and the smallest bar will have the lightest color.

Next, we will begin building some measures. The first measure will be a total of all the experience points (XP) for each character. Click the New Measure button on the Home ribbon and enter the following DAX expression:
```
Total XP = Sum(Characters[XP])
```
Now, add a Card visual and add the new measure we created Total XP.

Total XP Card Visual

This measure totals all the experience points for all the selected characters within the visual. Since all characters are now selected the total XP for all characters is 2,950.

The next, and final measure, will be the intelligent card. For this measure we want to display the characters name when we select them in the bar chart. Click the New Measure button on the Home ribbon and enter the following DAX expression:
```
Character(s) = IF( DISTINCTCOUNT(Characters[Pokemon]) = 1 , FIRSTNONBLANK('Characters'[Pokemon],'Characters'[Pokemon]) , DISTINCTCOUNT('Characters'[Pokemon]) & " Selected")
```
Update: As of Mid 2017 Microsoft introduced a new DAX expression called SELECTEDVALUE which greatly simplifies this equation. Below is an example of how you would change the DAX equation to use SELECTEDVALUE.
```
Selected = SELECTEDVALUE(  Characters[Pokemon],  DISTINCTCOUNT(  Characters[Pokemon]  )  &  " Selected" )
```
Explanation of this measure:

This measure first checks to see how many distinct items are in the column Pokemon of our dataset. If there is only one selected character then we will display the FIRSTNONBLANK character, which will be the name of our selected character. If there are more than one characters selected. The measure will count the number of characters selected and return a text string with the count and the word Selected. Thus, showing us how many items have been selected.

Add the measure titled Character(s) to a card visual.

Add Character Card Visual

We can now see that there are 4 characters selected. Clicking on Pikachu in the bar chart resolves with the character’s name being displayed and the XP of Pikachu being displayed in the Total XP card visual.

Selecting Pikachu

You can select multiple items by holding down Ctrl and clicking multiple items in the bar chart.

Well, that is it. I hope you enjoyed this Pokemon themed tutorial. Thanks for visiting.

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.
July 28, 2016
Measures – Dynamic Percent Change – Using DAX
This tutorial will produce a measure that will dynamically calculate a percent change every time an item is selected in a visual. The previous tutorial can be found here. In the previous tutorial we calculated the percent change between two time periods, 2014 and 2013. In practice it is not always desirable to force your measure to only look at two time periods. Rather it would be nice that your measure calculations change with changes in your selections on visuals. Thus, for this tutorial we will add some dynamic intelligence to the measures. Below is an example of what we will be building:

First here is the data we will be using. This data is the same data source as used in the previous % change tutorial. To make things easy I’ll give you the M code used to generate this query. Name this query Auto Production.
```
let
 Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Automotive_industry")),
 Data0 = Source{0}[Data],
 #"Removed Columns" = Table.RemoveColumns(Data0,{"Change", "Source"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Production", Int64.Type}, {"Year", Int64.Type}})
in
 #"Changed Type"
```
Note: the code shown above should be added as a blank query into the query editor. Add the code using the Advanced Editor. Another tutorial showing you how to add advanced editor code is here.

Once you’ve loaded the query called Auto Production. The Field list should look like the following:

Auto Production

Next add a Table with Production and Year. this will allow us to see the data we are working with. When you initially make the table the Year and Production columns are automatically summed, thus why there is one number under year and production.

Table of Data

Rather we want to see every year and the production values for each of those years. To change this view click on the triangle in the Values section of the Visualizations pane. This will reveal a list, in this list it shows that our numbers are aggregated by Sum change this to Don’t Summarize.

Change to Don’t Summarize

Now we have a nice list of yearly production levels with a total production at the bottom of our table.

Table of Production Values by Year

Next we will build our measure using DAX to calculate the percent changes by year. Our Calculation for % change is the following:
```
% Change  =  ( New Value / Old Value ) - 1
```
Below is the DAX statement we use as our measure. Copy the below statement into a new measure.
```
% Change = 
DIVIDE(
 CALCULATE(
 SUM('Auto Production'[Production]), 
 FILTER('Auto Production','Auto Production'[Year]=MAX('Auto Production'[Year])
 )
 ), 
 CALCULATE(
 SUM('Auto Production'[Production]),
 FILTER('Auto Production','Auto Production'[Year]=MIN('Auto Production'[Year])))
,0) 
- 1
```
I color coded the DAX expression between the two equations to show which parts correlated. Note we are using the DIVIDE function for division. This is important because if we run into a case where we have a denominator = 0 then an error is returned. Using DIVIDE allows us to return a zero instead of an error.

Next add our newly created measure as a Card.

Add Card

Change the % Change measure format from General to Percentage, do this on the Modeling ribbon under Formatting.

Change Measure Formatting

Next add a slicer for Year.

Slicer for Year

Now you can select different year and the % change will automatically change based on our selection. The % change will always select the smallest year’s production and the largest year’s production to calculate the % Change. By Selecting the Year 2013 and 2007, the percent change is 19.15%. The smallest year is 2007 and the largest is 2013.

Selecting Two Years

If we select a year between 2013 and 2007 the measure will not change.

Multiple Years Selected

The measure will only change when the starting and ending years are changed. By selecting the year 2014, the measure finally changes.

Selecting Additional Year

Pretty cool wouldn’t you say? Thanks for taking the time to walk through another tutorial with me.

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.
June 10, 2016
Measures – Calculating a Sum
Often there are times when you will want to display a totals. Using measures to calculate a total are extremely easy to use. The power of using a measure is when you are slicing and selecting different data points on a page. As you select different data points the sum will change to reflect the selected data. See sample of what we will be building today below.

Materials for this Tutorial are:
- Power BI Desktop (I’m using the April 2016 version, 2.34.4372.322) download the latest version from Microsoft Here.
- CSV file with data, download SampleData in CSV format.
To load the CSV file into Power BI Desktop you can follow along in this tutorial, Import CSV File into PowerBI Desktop.

Once you’ve loaded the CSV file into Power BI Desktop your fields items should resemble the following:

Fields List

Add the Table visual from the visualizations bar into the Page area. Drag the following items into the newly created table visualization, Category, Sales, and ID. Your table should look like the following:

Table of Data

Click the Triangle next to the ID column under the Values section in the Visualization bar. A menu will appear, select the top item labeled Don’t Summarize.

Do not Summarize Data for ID

This reveal all the unique items in our table of data. Now, we will create our measures for calculating totals. On the Home ribbon click the New Measure button. Enter in the following DAX expression.
```
Total Sales = SUM(SampleData[Sales])
```
Note: In the equation above everything before the equals sign is the name of the measure. All items after the equation sign is the DAX expression. In this case we are taking a SUM of all the items in the Table SampleData from the column labeled Sales.

This will total all the items in the sales column. Click on the Card visual and add the Total Sales measure to the card. Your new card should look like the following.

Total Sales Measure

Next we will add a bar chart to show how the data changes when the user selects various items on the page to filter down to different results. Add the Stacked Bar Chart to the page. In the Axis & Legend selectors add the Category column, and add the Sales column to the Value selector. This will yield the following bar chart.

Bar Chart

Now we can click on items in the bar chart to see how the table of data and the Total Sales changes for each selection. Clicking on the bar labeled Apples provides a total sales of 283, and clicking on the Oranges shows a total of 226.

Apples Bar Selected

Our measure is complete. Now we can select different visualizations and each time we do PowerBI is filtering the table of available data down to a smaller subset.

Pro Tip: When building different visuals and measures often it is helpful to have a table showing what data is being filtered when you interact with the different visuals. Sometimes the filters that you are applying by clicking on a visual interact in non-expected ways. The table helps you see these changes.

We have now completed a measure that is calculating a total of all the numeric values in one column.

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.
May 7, 2016
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

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

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

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

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

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

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

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

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

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.
April 27, 2016

Pokemon	XP
Pikachu	1200
Weedle	650
Pidgey	800
Golbat	300

Tag: Power BI Embedded

What you need

What Works

What is Needed

Close