The more you work with Power BI Desktop it is more than likely you will find some tool limitations that impact your overall design pursuits. As I have worked with data visualization software, I find there is always a balance between what I want to make and what is possible. The more you become familiar with your visualization tool, the better you think of report designs that are both beautiful and feasible. One such design style that I use is to limit the use of slicers on the report page as much as possible. My solution for removing slicers is adding a stacked bar chart or a stacked column chart. The chart can be used as a slicer because you can click on the data bars and filter the page of data. One of the visualization limitations I’ve had to work around was the ability to make a stacked bar chart with long y-axis titles. In order to overcome this limitation we need to fire up our creativity to figure out another way to more accurately control the y-axis labels and the associated bar chart.
There are two main issues we will need to solve:
First issue, when you have text along the y-axis in the stacked bar chart, the text becomes truncated. See below.
Stacked Bar Chart
Note: All the text next to each bar is truncated if the text is to long. This can be fixed by extending the visual to a ridiculous length, as illustrated by the following picture. While this solves the text issue, this totally defeats the purpose of this visual, provide a “slicer” that can be used to filter the report page with minimal space consumption.
Super Long Stacked Bar Chart
Second issue, when there are super small values next to large values, it is almost nearly impossible to click on the bar to enable the filtering. In the example image above, it’s easy to click on the value of 1,300 but almost impossible to click on the value of 10. Womp, Womp, and clicking on the bar text value does not enable filtering, insert ridiculous horn sound, or other familiar but annoying horn sound (as recommended by one of our readers Terence).
After some playing around with various visuals here is what I came up with. First, you must change the visual from a stacked bar chart to the matrix visual. On the Visualizations pane click on the Matrix visual.
Matrix Visual
This will change the visual to a matrix. It’s a little busy so we will clean it up a bit. On the Visualizations pane change the Matrix style to None, then open up the Subtotals section and set Row subtotals to Off. Your visual should now look similar to the following:
Change Matrix Style
Next we will add the “bars” to the visual. Open the Conditional formatting section and turn Data bars to On.
Turn on Data Bars
Short and sweet. Now we can properly resize the “text labels” of the y-axis and when we try to select small values such as 10, we are presented with a little grey selector bar, enabling us to select very small values.
Grey Selector Highlighting Bar
When you compare all three items side by side you can see that the most condensed version is the Matrix visual with conditional formatting bars. This provides you much more control when dealing with data that contains long text labels.
Comparison of Bar Charts
Note: There are many ways you can format your matrix to get the desired look. This tutorial is simply covering one type of look. Additionally, you could hide the text and grid completely by making the grid and column title colors of those match the color of your background, or use could choose one of the many of the grid type options to fit your style needs.
Thanks for following along, as always if you found this helpful please share it with someone who might find this helpful.
There are many cases when you will need to create a date table within Power BI desktop. This could be as simple as creating a master date table or more complex such as creating a monthly or weekly index number tied to a date. To create a date table there are two methods for creating a date table. Method one, create the table directly in the Power BI Desktop, or method two load the date table from the data source.
For this tutorial we will walk through a couple different examples that are specifically addressing creating a date calendar via DAX expressions.
Let’s begin by making a basic table. Open Power BI Desktop, on the Modeling ribbon click New Table.
New Table
In the formula bar enter the following DAX expression:
Dates =
GENERATE (
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
This generates a simple date table. Let’s walk through what is happening here.
The CALENDAR DAX function generates a table with a list of dates from Jan 1 to Dec 31 of 2017.
We define variables (denoted by VAR) to capture details from the column named [Date] that is created by the CALENDAR function.
The Return function generates one row at a time. The row iterates through each [Date] item in the list which was created by the CALENDAR function. Variables are re-calculated for every row execution.
Note: When creating DAX tables as we are doing so in this example, the DAX table only refreshes when the report refreshes. Thus, if you want the date list to increase over time, or your using a NOW() in the DAX table you will need to be sure to schedule refreshes for the Power BI report in the PowerBI.com service.
By contrast we can also generate the same data table by calculating our data column by column. Again, on the Modeling ribbon click the New Table icon and add the following DAX:
While this is great, we have a date table now, but what we lack is flexibility and automatic time intelligence. One option to change this table to auto detect dates within your data model is to replace the CALENDAR DAX statement with CALENDARAUTO().
To use CALENDARAUTO we need to supply a table with a column of dates. We will quickly create a dummy data table with a couple of dates, so we can use CALENDARAUTIO.
Click Enter Data on the Home ribbon. Enter the following information into the Create Table screen. Click Load to add this data to the data model.
Enter Date Table
Now that we have loaded a table into the model with two dates, we can add our new date table. On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 3 =
GENERATE (
CALENDARAUTO(),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
Note: In the MyData table we added two dates, 3/3/2017 and 10/30/2017. When we look at the included dates in the new Date 3 table we have every date listed from January 1 to December 31st. This is because the DAX function CALENDARAUTO will return the entire year of calendar dates even if it only finds one date within a given year period of time.
Let’s say we want to build a date calendar that will automatically grow and change over time. We want to identify today’s date and then create a list of dates for the previous year.
Moving back to generating a date table by rows we can now use the DAX NOW function. On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 4 =
GENERATE (
CALENDAR( DATE( YEAR( NOW() ) - 1, MONTH( NOW() ), DAY( NOW()) ), NOW()),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
Note: In this DAX table we used the NOW() function which returns a date and time. The same can be done when using the TODAY() function which only returns the date and not the time.
This now generates is a date table that starts one year ago and populates all the dates until today. For example, if today is 10-29-2017, then the date list would start at 10-29-2016 and end on 10-29-2017. Pretty cool…
Let us move further down the rabbit hole. We can also start adding calculations that helps us move through date time calculations. For example, you may want to calculate this month’s total sales and possibly last month’s sales. By adding columns with an index, you can quickly shift time periods. Doing so makes time calculations much easier.
On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 5 =
GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
)
)
Note: The DAX equation above will work in your report without any changes. However, I made a variable called startOfWeek. This variable allows you to define the start day of the week. For example, if you data starts a new week on Sunday, then the startOfWeek number will be a 1. If your data start of week begins on Wednesday then the start of week number would be a 4. This allows you to auto detect the day of the week and then automatically arranges all your weekly index numbers in the correct format. Try playing around with this variable to see how DAX table changes.
So why work so hard on the date table? Well by having a robust date table you can simplify many of your measures that you need to build for your report. Consider the following example:
You have a Sales table with a date and sales column.
Sample Sales Data
And you have our fancy Dates 5 Table we created earlier:
Date 5 Calendar
The Date 5 table is linked to the Sample Sales table:
Date and Sales Tables Linked
You can now build the following DAX measures inside the Sample Sales table:
The nice thing about these measures is that every time the data set refreshes the dates will automatically recalculate the last week and last five weeks.
If you want to be able to handle the additional filter context of the visual, you can pick up the visual filter context using variables (VAR). Then you can RETURN a calculate function that will shift all your time ranges for you.
Well that is about it. Thanks for following along.
I am so thankful you have taken the time to read my tutorial. My hope is that by using these free tutorials you can become a rock-star at work. In order to keep these tutorials free please consider purchasing the Power BI Desktop file for this tutorial. Come on it’s only a dollar, I mean you spent than that on your coffee this morning.
You can pay with your PayPal account or via credit card
Get Data – Power BI Connection Types: An Introduction
Hi, I’m Seth, I am very excited to be a contributing on PowerBI.tips. Mike has done an incredible job curating fantastic content for the PowerBI Community. In this first blog I will introduce you to the different types of connections that you can make using the Power BI Desktop. We will identify the various types of connections. In future posts we will dive into specific examples of usage and tips in tricks.
When I say “Types”, I don’t mean connecting to databases, Excel, SharePoint, etc. Those are just different data sources. I’m referring how Power BI ingests or interacts with data sources that you want to connect to. Believe it or not, Power BI doesn’t always have to pull all your data into the Power BI Desktop file. Depending on what sources of data you are connecting to, you could not even realize that there are more options, or be uncertain of what they do. In fact, depending on what type of connection you choose you are also altering how the Power BI Desktop functionality works! Now that I have your attention, let’s jump into the good stuff.
First things first. The only time you will be faced with an option to choose a type of connection, are when you connect to a data source that support multiple connection types. If all you connect to is Excel, you would never see an option in the dialogues because it only supports one type of connection.
There are really 3 main types of connections. The first is the most widely used, and is the default when connecting to most data sources. It is Import. This connection will ingest or pull the data from the data source and become part of the PBI Desktop file. An example of where you would select import Is in the SQL Server dialog box.
SQL Server Import
You can import data from a SQL Server by clicking Get Data on the Home ribbon.
Get Date SQL Server
The import connection type allows you to use the full capabilities of the Power BI Desktop and you can manipulate it however you see fit. A way to validate this is by looking at the left-hand navigation and you will see three selections. The top selection which resembles a bar chart is the Report Page. This is where you would place all your visuals and develop your report pages. The second item from the top, which looks like a table is just that, the Data view in a table form. This lets you see all the data contained with a loaded data table. Finally, at the very bottom, the relationships selection. This is where you will see multiple tables and the connections between the tables. The relationships section feels like working SQL or in Microsoft Access.
Import Options
The 2nd connection type is Direct Query. Notice in direct query mode the third item, relationships has been removed. The direct query connection type is only available when you connect to certain data sources. The list of the data sources that are accessed using direct query can be found here. This connection is unique in that the data does not get loaded into the PBI Desktop. What happens, is that Power BI can communicate in the language of the data source and request information as you interact with your Power BI Visuals. The useful thing about this connection is that the data never leaves the data sources, it is only queried. Direct Query does limit what you can do from a data manipulation perspective. Power BI assumes you are already doing all the necessary data manipulations in your source. As a result, you don’t even have the option to mashup data and that selection is removed in the left-hand nav.
Direct Query Options
The 3rd type is Live Connection. There are only 3 data sources that support the live connection method at this time. All of them are a type of (SSAS) SQL Server Analysis Services. Those types are Multidimensional, Azure Tabular and Tabular on premises. The live connection type is the most unique in that it recognizes the full model or cube that you’ve created. Power BI Desktop turns off all data prep features. Thus, the user is given a bare minimum in formatting and report side calculations. All the heavy lifting is done on the server that supports the model and Power BI is only used as a reporting tool. This connection is used mainly by IT and enterprise implementations. If one looks at the left-hand navigation, you quickly realize that it is the most restrictive in terms of what can be done in the Desktop itself.
There is a fourth Live Connection that defaults to the connection type, and this occurs when you use the Power BI Service as a data source. This connection is using a SSAS connection, only the end users don’t need to set anything up other than having dataset to connect to in the Service.
Live Connection Options
Finally, there are two types of connections that dive a bit deeper than what comes with the Desktop out of the box. Those are Custom Data Connectors and API/Streaming. For the time being, we’ll leave these as just high-level points for now, and dive deeper into them in the specific articles in the future.
I hope you’ve found this initial primer useful. As this series continues we’ll dive into some of the reasons for using each of these types of connections, why you would want to, and the positives and negatives in choosing which one, provided you have a choice.
DAX (Database Access Expressions) can be quite complex. It is essential to being able to appropriately manipulate the Power BI data model for the visuals. The following information was discussed at the Power BI World Tour 2017 and if you’d like to review the content or learn more about DAX hit some of the links below. For all who were able to attend, you were a great audience and super fun! I hope you learn some fun facts and tricks.
To everyone who was able to attend the Power BI World Tour 2017, Thank you! It has been a pleasure presenting my favorite tips and trips. This post consolidates all the links for each topic that was discussed. All the links to the content are below, if you want to go through the tutorials at your own pace hit a link below. When going through 7 topics in an hour, it is difficult to completely learn everything. So, if your like me, you learn by doing, making mistakes and gritting it out until you understand. Enjoy and have fun.
I’ve been using Power BI since it was released back in 2015, and I’ve found that when talking with other PowerBI users there is always a little confusion about how to share Power BI reports. My experience has been that most people are first introduced to the service and go directly to PowerBI.com, login and start playing around. They explore a little and find a cool data source to connect to, such as a SQL database or some good old google analytics data. Typically they will begin making a dashboard and really get into making visuals on a report.
While this is an excellent way to get started because this removes the complexity of having to model your data, it does come with some challenges that leads to issues later on.
There comes a point in time that the user usually needs some additional data. The “this is good data but, if I could only join the data with another source….” question almost always comes up.
These reports are typically made in your personal workspace which is not a good solution for sharing a report with another user. I’ll explain later.
How do I source control this document? What if I want to go back in time and restore my changes?
In order to answer these questions, I’ll show you what I use to get around these issues.
To start off, and most importantly!, try to refrain from building your dashboards in the PowerBI.com service. Yes, you can do it, but it is not recommended. I don’t recommend this because any report created in your personal workspace can only be edited by you and no one else. Also, if your account is deleted or you loose access to your account no other PowerBI user can modify your original report. It’s the classic, win the lottery, or hit by a bus case, depending if your an optimist or pessimist. What you should do instead, is download and open up the PowerBI desktop application. This will save you ton’s of headache later when you want to join multiple data sources or want to have a saved copy of your Report.
After you download the desktop application you will load the data using the Get Data button found on the Home ribbon.
Get Data Button
Almost, all data sources found in the Get Data window are on the PowerBI.com service. You will notice this changes over time, with each monthly release of PowerBI desktop.
FYI, I have noticed that changes happen faster in the PowerBI.com service as it appears as though there are weekly or bi-weekly updates. But the Desktop version is slightly slower to get changes as builds are only released once a month.
Get Data Window
Next you will load data into the Power BI desktop. If you want to learn more about modeling you can browse through these tutorials on data modeling. Modeling happens in the query editor with the M language, and on the report pages using DAX.
Now we will create a very simple PBIX file to publish to the PowerBI.com service.
On the Home ribbon click the Enter Data button and enter the following:
Create Table of Data
This will create a simple data table. Click Load to bring the data into PowerBI Desktop.
Create a visual by clicking on the Stacked ColumnChart and adding the following columns to the visual.
Add Column Chart
Congratulations, we have completed a very simple report and you have loaded data inside the desktop. In addition to loading data PowerBI is equipped with a robust modeling tool called the Query Editor. The very topic of modeling your data is a huge. It covers the Query Editor, making DAX expression and has been covered in numerous books. This this post will not address modeling your data. As a side note, here are some really good sources of information for learning more about DAX.
Alright, let publish this bad boy. First save the Report. Do this by clicking File then click Save. Enter a simple name such as Sample Report, click Save.
Save File
To publish the report to the PowerBI.com Service click the Publish button found on the Home ribbon.
Publish Button
If you have not already signed into the PowerBI.com service you will be prompted to sign in.
Sign In Screen
If you already have a more than one workspace in the PowerBI.com service a second window will pop up asking you to choose a workspace location. Choose a workspace location to publish the report.
Choose a Workspace
Couple of important notes about workspaces.
A workspace can have admin users or members. The admin user can add and remove individuals to the workspace. A member can be configured to either, edit reports, or only view reports.
The workspace can have many admins if necessary which is helpful when collaboratively working on reports with a team.
I have found that when working on many different reporting projects a workspace helps target a specific audience or provides an organized method to share only reports that would be relevant.
You cannot create a workspace in this Publish to Power BI window. This has to be done from the service.
Since I am not sharing this report I will publish to My workspace. Click Select to complete the publishing. Once the report is done loading you will have a window that notifies you that the process has completed. A link is provided to check out your report directly on the service. You can dismiss the message by clicking Got it.
Link to Report
Click Got it to dismiss the pop-up window.
While remaining in PowerBI desktop, save the file one more time but use SAVE AS and save the file with the name of Sample Report V2.
Now we will create a new workspace for a report that we might want to share.
Go to PowerBI.com and login. On the left navigation bar click the arrow to expand the workspaces.
Open Workspaces
A fly-out menu will appear and you can click Create App Workspace.
Create App Workspace
Fill out your workspace name and configure your settings. Be sure to enter your email address that you used to log into the PowerBI.com service in the workspace members area.
Create Workspace
At the bottom of the menu click Save when you have completely configured the settings. Opening up the workspaces fly-out menu will now reveal your new workspace.
App Workspace
Click on the ellipsis of the new workspace PowerBI-test.
Click on Ellipsis
Notice when you click the ellipsis, that there are only two options, Edit and Leave. If you give it about 10 minutes or so, a third option will show up, Files. The reason we have to wait is because behind the scenes this workspace is tied to 0365 Groups. Thus, we have to wait for all the services to be fully provisioned before we can proceed. We will want to use the files option of our workspace. Now would be a good time to take a little potty break, pet your dog, get a snack… and come back in 5.
Yumm, I love Oreos…
Now that we have waited a bit, Refresh your browser page. This is important, if you don’t do this you won’t be able to observe the change. Click the ellipsis again and now we have a new item calledFiles.
Workspace Files
Click on Files and you will be re-directed to a custom SharePoint 0365 page for this workspace’s files.
Now we can upload the version two of our PBIX file. Click the upload button on the navigation bar.
Upload File
After uploading your file it should look like the following:
Loaded File
Go back to PowerBI.com, make sure you are still in the created workspace, then click Get Data at the bottom of the left navigation bar. Then click Get in the Files window.
Get Files Screen
Then a screen will pop-up showing you which source you can use to get data. Click the title labeled OneDrive.
Get One Drive Files
Upon clicking this we are taken to the OneDrive location that we created with the workspace. In here we will have the Sample Report V2.pbix file. Click the name of the file Sample Report V2.pbix. Then click Connect to load the file.
Note: If you want to make changes to the pbix file you have to overwrite the file in the OneDrive location. Don’t worry, SharePoint does have version control on the files. This will keep the files location nice and clean. It also removes the need for you to version or date each and every file. To learn more about versioning files in SharePoint hit this link.
Now when we click on the workspace PowerBI-test we can see that the dataset, report, and dashboard has all loaded.
Workspace
There are some advantages of loading your pbix file this way.
loading a file from OneDrive will automatically refresh the report every hour from the pbix file.
If any team members want to modify the original file the members of the workspace can access the file from the SharePoint page.
Click on the Ellipsis of the Sample Report V2 under the DATASETS section and select Schedule Refresh from the fly-out window. You will notice just under schedule refresh is a new setting called OneDrive refresh.
OneDrive Refresh
Click OneDrive Refresh, and now you can see there is a toggle for keeping the file refreshed every hour, which is enabled by default.
Hourly Refresh
Whew, I think that about wraps it up, start with the desktop to make your files, be sure to create workspaces to share content, and use the workspace files to store and source control your pbix files.
I hope it was helpful.. Make sure you share this blog with another person if you found it valuable.
In many reports we produce we often need a method to score or rank data. For example, we may need to list the sales totals for the sales team and rank them from highest sales to lowest sales. Ranking can be done as a calculated column, or as a measure. When using a measure, the ranking becomes dynamic and takes on the filter context of the table, or visual, that is showing the data. Calculating a rank as a measure can be useful if you want to allow the user to select different categorical values such as product type and then have the report automatically rank the selected items. When the report filter context changes the items are automatically re-ranked.
Alright, let’s jump into the data!
Open PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. On the View ribbon click the Advanced Editor button. 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/2017/05/Clothing-Sales.xlsx"), null, true),
ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"
Once you have copied the m code above into the query editor click Done.
Clothing Sales Data
Be sure to name your query Clothing Sales. Then on the Home ribbon click Close & Apply to load the data into the data model.
To understand how the ranking will work we must first understand the DAX function ALLSELECTED. You can read more about the Microsoft documentation on this function here.
To illustrate how the ALLSELECTED() function works we will make two measures and place them in a simple table.
Begin by creating a sum of the Sales in the Clothing Sales table. Click New Measure on the Home ribbon. Enter in the following measure equation:
Total Sales = SUM ( ‘Clothing Sales'[Sales] )
Now, create a Table visual with the selected columns shown in the image below.
Add Table Visual
Sweet, we can see that all the categorical items have been added together forming totals. Add the Slicer visual for the Category column, see example below.
Add Slicer
Once the slicer is added we can select various items and see our table filter correctly.
Using the Slicer
Note: if you want to select multiple items in the slicer, hold the ctrl key and click on the multiple items that you want to select. This is how I selected the multiple items in the image above.
Now, let us make a measure doing the same calculation but this time we will apply the ALLSELECTED() DAX function. Click on New Measure on the Home ribbon and enter the following DAX formula.
In this new formula we are calculating the sum of all the clothing sales but using the filter context of all the items selected from our filters. Notice with nothing selected in our slicers that the sum of all Total Sales 55k, is the same for each row of the table for the column Total Sales ALLSELECTED. This is due to the fact that we changed the filter context for the sum calculation.
Select Jeans and Pants from the slicer. Notice we have the same results but with different totals. The totals calculated using ALLSELECTED ignored the filter context of jeans and pants and calculated the total of all the selected sales.
Select Jeans and Pants
Finally, we will now add the Ranking. To calculate the rank we use the DAX function RANKX(). More documentation can be found on RANKX here.
Add the new measure, Ranking, to the table visual. Ta Da, automatic ranking based on information that was selected from our slicer visual.
Adding Ranking Measure
Note: when we used the RANKX function we called out a specific column the Category column from our Clothing Sales table. If you only specify the table name this measure will not work. We are using the filter context of the categories to conduct the ranking operation.
For those of you who work in supply chain management this tutorial will be right up your alley. In my previous job position I had a lot of interaction with our shipping department. We would look at when orders were placed from the customer, and conduct a comparison to what orders were actually shipped or cancelled prior to shipment. Our analytics team would produce reports and metrics to our customers about orders and shipment information.
In an ideal world, every product ordered on the purchase order would be shipped and some point in the future. But, as we know, in the real world this isn’t always the case. Orders get cancelled, products get re-ordered, challenges happen, and therefore we would need to track all these changes. In our shipping analytics group, the team would pull data from our shipping system with columns similar to the following:
Order Date, Ship date, Product type, and Shipped QTY
Sometimes you want to sum the data by the order date, and in other cases you want a total by the shipped date.
In this example, we will walk through making a measure that uses the DAX formula USERELATIONSHIP. To learn more about this function from the Microsoft documentation follow this link.
Open PowerBI Desktop, click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. On the View ribbon click the Advanced Editor button. 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/2017/06/Clothing-Sales-Ship-Order-Dates.xlsx"), null, true),
ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Order Date", type date}, {"Ship Date", type date}, {"Category", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"
Your loaded data should look like the following:
Sales Data Load
Click Close & Apply on the Home ribbon to load the data into the data model.
We will want to create two measures, one that performs a calculation on the Order Date column, and one on the Ship Date. To do this we need a date table to populate all the dates needed for this data set.
We can do this by creating a DAX date table.
On the Modeling ribbon click New Table.
New DAX Table
In the formula bar enter the following.
DateList =
GENERATE (
CALENDAR ( DATE ( 2012, 1, 1 ), DATE ( 2017, 12, 31 ) ),
VAR currentDay = [Date]
VAR startYear = 2012 // we know this by looking at our data
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
“month”, month,
“year”, year,
“month index”, INT ( ( year – startYear ) * 12 + month ),
“YearMonth”, year * 100 + month )
)
Note: This DAX formula is building a date table, for each row we are building the columns, Month, Year, Month Index, and an integer for YearMonth index. This is a simple way to repeatedly create a date calendar based on your data.
Great, we have completed the data loading. Now, we need to link the date table to the Clothing Sales data. To do this click on the Relationships button on the black navigation bar located on the left side of the screen. Then Click & Drag the Date column from the DateList table to the Order Date column of the ClothingSales table. This will create a one to one relationship link between the two tables. Note that the relationship is illustrated in a solid white line. This means it is an active relationship.
Next, drag the Date column from the DateList table to the Ship Date column of the ClothingSales table. We have made our second connection. Note that this connection has dotted white line. This means this connection is not active. Also, we can observe that the relationship between the two tables, DateList and ClothingSales is a one to many relationship. This is denoted by the * on the ClothingSales table, and the (1) one on the DateList table. The * means there are duplicate values found in the ClothingSales table. The (1) on the DateList table means in the Date column we only have unique values, no duplicates.
Note: You can edit the connections between tables by double clicking the connecting wires. This brings up the Edit Relationship dialog box which allows you to edit things like, the Cardinality, Cross Filter Direction and activating / deactivating the connection.
Once you’re done your relationships should look like the following:
Linked Tables
By default, Power BI will only allow one active connection between tables. Therefore, we have one connection active and the other has been inactivated by default. Return to the report view by clicking the Report icon on the left black navigation bar.
Report View
Now that we have completed the data modeling let’s make some visuals. We will start by making a simple table to see what the data is doing. Add the columns from the two tables, ClothingSales and DateList to a Table Visual.
Create Table Visualization
Great! Now we have the total number of sales based on the order date. We know this because it is the primary connection that we established earlier when we linked our two tables together. But, what if I wanted to know the sales that were shipped based on the Ship Date. Earlier we made this connection but it is inactive.
Here is the awesomeness! We can create a measure that calculates different results between a user specified relationship.
First, we will re-calculate the sales number that we already have in our table. On the Home ribbon click the New Measure and enter the following in the DAX formula bar:
Order Date Sales =
CALCULATE (
SUM ( ClothingSales[Sales] ),
USERELATIONSHIP ( ‘DateList ‘[Date], ClothingSales[Order Date] )
)
Note: In this DAX formula we are creating a explicit measure, meaning we are specifically telling Power BI to sum a column. An implicit calculation is what we did earlier when we added the sales column to the table.
The USERELATIONSHIP filter within the calculation forces Power BI to calculate the sum based on the dates listed in the Order Date column. To see another demo on UseRelationship you can watch this video from Curbal.
Create another measure with the following DAX formula:
Ship Date Sales =
CALCULATE (
SUM ( ClothingSales[Sales] ),
USERELATIONSHIP ( ‘DateList ‘[Date], ClothingSales[Ship Date] )
)
This time we are forcing Power BI to use the inactive relationship to calculate the sum of the sales by shipped date. Add the two new measures to our table and we now can see how the calculations differ.
Sales By Order & Ship Date
The calculated sales for the order dates match our earlier column. This is expected, and we can confirm that this calculation is working properly. The shipped date sales are now calculating a different number. In some cases, the Shipped Date Sales is lower than the orders, because in that month you took in more orders than you shipped. In other months, the Shipped Date Sales is higher than the Order Date Sales, because there were likely large shipments ordered in the prior month and shipped in a different month.
By adding a Bar Chart from the Visualizations pane, we can now see sales by order date and ship date.
Bar Chart by Year
We can even dig deeper into the data. Click the Expand button to see the data by Year and Month.
Expand Button
Well that is about it. I hope you enjoyed this tutorial about using two relationships between data tables. If you want more information about DAX check out these books that I have found extremely helpful.
This year I had the wonderful privilege to attend the Microsoft Data Insights summit for 2017 as a speaker. My topic was the Top Ten Tutorials from PowerBI.Tips. So, let me say thank you to all those who attended you provided a very fun audience and were very positive, Thanks. For those of you who couldn’t make it to the Data Insights Summit below is the actual talk:
We went through 10 different tutorials in 50 minutes. Needless to say we went pretty quick. So, if you were there, or just want to go through the tutorials on your own here is the list of the Top Ten Tutorials.
Every so often you find a hidden gem, something so valuable, just hiding there in plain sight. Recently, I found out that I LOVE the using Variables with in a DAX expression. Ok, brief introduction, I was trying to calculate sales changes between different years. The data looked similar to the following table:
Year
Month
Sales
2013
1
20
2013
2
25
2014
1
22
2014
2
23
2015
1
21
2015
2
31
I wanted to sum the sales for each year and compare the percent change between each year. If you have followed my blog for a while, I have a ton of tutorials about percent change. ( See list of other tutorials dealing with percent change )
Now, for the twist making this calculation more difficult. I want to calculate percent change even when a filter is applied by clicking on a visual. By using variables we can apply some data modeling voodoo magic to get the desired results. See a sample of the measures working in the Power BI Embedded sample below:
Let’s dive in!
Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. On the View ribbon clickthe AdvancedEditor button. 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/2017/05/Clothing-Sales.xlsx"), null, true),
ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number)
in
#"Inserted Year"
Your loaded data should look like the following:
Load ClothingSales Data
Rename the Query to ClothingSales, and click Close & Apply to load the data into the data model.
It is a good practice to review the data columns and verify that the formatting is set up correctly. To do this click on the the column Date in the Fields pane, then click on the Modeling ribbon. In the Formatting section change the format to Date Time and select M/d/yyyy format. Repeat this for the following columns:
Format Sales to Currency then select $ English (United States)
Format Year to Whole Number
With our data properly formatted we are ready to build a simple bar chart. See the image below for the bar chart set up:
Create Bar Chart
To start we will create a explicit calculation for the total selected sales. On the Home ribbon click the New Measure button and enter the following code:
Total Selected Sales = SUM(ClothingSales[Sales])
Pro Tip: To learn more about explicit vs implicit calculations within Power BI Click the following link.
Add a Card visual and add our new measure.
Add Card Visual
Let’s change the formatting measure so we can see the full number. With the Card still selected click the Paint Roller expand the section labeled Data label and change the units from Auto to None. This allows us to see the entire sales number.
Remove Number Formatting from Card
Let’s enhance this card visual further by adding dollars formatting. Make sure you have the Total Selected Sales measure selected by Clicking on the words of the measure. Then, click on the Modeling ribbon click and change the Format to Currency, then select $ English (United States). The final format of the card visual should look like the image below:
Final Card Format
For the next step we will repeat the previous steps for our new measures.
Create the measure
Change the formatting of the card
Change the formatting of the measure
The next measure will calculate the prior year total sales, but only calculate this value when a filter context from a different visual is applied. Take note this is the magic I was talking about!!
Prior Year Sales =
VAR
selectedYear = if( HASONEVALUE(ClothingSales[Year]),
FIRSTNONBLANK(ClothingSales[Year],ClothingSales[Year]),
BLANK() )-1
Return
CALCULATE(
SUM(ClothingSales[Sales]),
ALL(ClothingSales),
ClothingSales[Year] = selectedYear)
I am going to pause a second here and explain what is going on in this measure as we have multiple things going on. First, we are creating a variable with the VAR. In the below image I show you that the variable that we are defining is called selectedYear. SelectedYear returns a value of blank if multiple years are selected. This is accomplished by using the if statement and the HASONEVALUE() DAX formula. The Return in then allows us to output a value. In this case we are using the CALCULATE() to sum all the sales. The next part is crucial to making this work. Within the Calculate we are applying two filters. The first filter selects the entire table with the All() then we apply the filter from the earlier defined variable with the name of selectedYear.
Prior Year Sales in Color
Apply the same formatting steps 2, and 3 mentioned earlier. Now, we can select one of the years within our bar chart. Doing so populates the two measures we created. The selected year total sales, and the prior year of sales. I selected the year of 2014, which has sales of $11k. Hover your mouse over the bar for year 2013 and the tool tip will appear showing a total sales of $10K.
Selected Bar
Now we will make our percent change measure. Enter the following as a new measure:
Percent Change = DIVIDE([Total Selected Sales],[Prior Year Sales],1)-1
Pro Tip: Use the DAX function DIVIDE when dividing two numbers, this protects against odd cases where the denominator is zero.
Click on the Modeling ribbon and change the formatting of the measure to Percentage. Add a final measure that calculates the difference. See measure below:
Prior Year Delta = [Total Selected Sales] - if( [Prior Year Sales] = BLANK(), [Total Selected Sales],[Prior Year Sales])
This measure calculates the total changes in sales between the two selected years. The if statement at the end of the equation handles a special case that if you don’t have any individual year selected the prior year delta defaults to zero.
Thanks for following along an learning about variables. Trust me, these are super helpful. Make sure you spend some learning how to leverage variables in your DAX equations.
This Tutorial was inspired by the following article I found from SQLBI authored by the master of DAX Alberto Ferrari. I mean, come on, Ferrari, how baller is that last name! I guess that is why he is the master. Kudos to you Alberto!
Still need more reading about DAX, check out the following books:
