PowerBI.tips

Category: Building Reports

Power BI Connections: Direct Query
Now that we’ve solidly gotten through the basics in terms of what connection types are in the opening blog, found here, and detailed out what is included in the default connection type of Import found here, let’s get on with some of the more interesting connections.

Connection Type Outline

Direct Query is the first connection type that we will discuss that extends, but at the same time limits functionality in the tool itself. In terms of data access this connection type allows us to access our data in the source system. The supported data sources for direct query can be found here. This is distinctly different than what we observed in the import method. When using Import, the data is a snapshot and refreshed on a periodic basis, but with Direct Query it is live. “Live” means that with Direct Query the data stays in the source system and Power BI sends queries to the source system to return only the data it needs in order to display the visualizations properly. There are some pros and cons in using this connection so it is important to understand when you might use it, and when you should probably avoid this connection.

Cons:
- When Direct Query is used you can no longer do many of the data mashup actions in the “Edit Queries” section of Power BI. It is assumed that you will have already done this in the backend. You can do simple actions such as removing columns, but don’t expect to be able to manipulate the data much. The Query Editor will allow you to make transformations, but when you try to load to the model you will most likely get an error that looks something like this
Direct Query Error
- The data tab is also disabled in the model layer and thus you need to make sure that all the formatting and data transformations are completed in the source.
Data Tab No Longer Present
- You can do some minor adjustments to format, but this could be a heavy restriction if you don’t have access to the data source.
- There are performance impacts to the report that need to be taken into consideration. How large is the audience that will be interacting with the report? How busy is the source system, are there other processes that could be impacted?
- Troubleshooting skills in source system language
- Multiple applications required to adjust data ingestion and formatting
Pros:
- The Direct Query connection does not store any data. It constantly sends queries to the source to display the visuals with the appropriate filter contexts selected.
- In the November 2017 release there is a new capability in Power BI allows you to reduce the traffic and enhance this connection method exponentially. The feature is called Query reduction, and allows you to enable an “apply” button on a slicer or filter. The benefit with this option is that you can set all your selections of a filter prior to Power BI executing the query. Before this feature was available, every selection you made would fire off a query to the source database. To enable this feature, go to File -> Options and Settings -> Options -> Query Reduction you will find these options to help with Direct Query Performance.
Query Reduction Options

Note: This enhancement greatly increases the performance of a Power BI report against the data source, but be aware that there could be poor query performance, or aspects of the solution that would require troubleshooting in the data source depending on what queries are being passed. This would require understanding of how to performance tune the source.
- Deployment of the Direct Query connection requires the use of the gateway previously called the Enterprise Gateway. Note that the Enterprise Gateway is different than the personal Gateway.
- No data is ingested into the model using Direct Query thus, there is no need to schedule a refresh. Once the dataset is connected to the Gateway, the data source feeds information to the report as the user interacts with the report.
- It will always show the latest information when you are interacting with the report.
Direct Query is a powerful connection type in that it produces the most up to date data. However, as we have seen, it does come with some considerations that need to be taken into account. The Pros and Cons of the connection mostly revolve around whether or not the end user can understand and deal with potential performance issues, updating data retrieval processes, and understand the downstream implications of a wider audience. Typically, Direct Query is used in extremely large datasets, or in reports that require the most up to date information. It will most likely always perform slower than an import connection and requires an understanding of tuning and troubleshooting of the data source to alleviate any performance issues.
December 29, 2017
Start of Month DAX Calendar
While on a recent project I needed to build a variation of the DAX date table. In my previous post, found here Creating DAX Date Tables I was built a date table for every day of the month for multiple years. I’ve only ever needed to consume a fully populated date calendar, but in this instance because the data I was collecting was already aggregated to the first of the month I only needed a date calendar with each month’s start date. After some playing around with my previous DAX functions I think I was able to come up with an elegant solution.

Let’s get into it.

Let’s begin by making a basic table. Open Power BI Desktop, on the Modeling ribbon click New Table.

Enter the following code:
```
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 will produce our standard table from January 1st to December 31st, 2017 with every day populated in between. In, addition we get numerical values for Day, Month and Year.

Note: This table is producing a list of dates using the Calendar DAX function then iterating row by row to calculate each numerical value for the Day, Month and Year.

Add the Table visual with the following Values from our created DAX table.

Dates Table

Note: When you drag over the field labeled Date the field will be naturally added as a Date Hierarchy. To remove the Date Hierarchy, you have to click the little drop down arrow next to the word DATE and select the word Date in the drop down menu. This will remove the Year, Quarter, Month and Day date context from the visual.

The date calendar we made has every date, but we want only the first of each month.

Lets build a new table by following the previous steps and adding the following:
```
Start of Month Dates =
  GENERATE (
    GENERATESERIES(1,12),
    VAR inc = [Value]
  RETURN ROW (
    "date", DATE(2017,inc,1)
    )
  )
```
Add the Table visual to the report page and add the following fields:

Start of Month Date Table

Note: I already removed the Date Hierarchy using the instructions listed above in the previous note.

This new DAX Date table is first generating a list of numbers 1 to 12 one for each month. Then it iterates through the list and produces a date using the Date function were we manually provide the Year, and the day. You can see the Generate function produces a column of data called [Value]. The variable denoted by VAR inc is capturing the number for each month. So, now what if we want to produce more than one year of dates? Super simple, just change the generate series from 1 to 12 to 1 to 48. This will produce three years of dates.

Change your Start of Months Dates to the following:
```
Start of Month Dates =
  GENERATE (
    GENERATESERIES(1,48),
    VAR inc = [Value]
  RETURN ROW (
    "date", DATE(2017,inc,1)
    )
  )
```
With one number change we can produce 4 years of dates.

Four Years of Dates

Cool, let’s go a little further. Just in case we need it we can also produce a list of dates that contain the end of the month. Add the following your Start of Month Dates with the following DAX table (don’t forget the comma on line 1 in the ROW function):
```
Start of Month Dates =
  GENERATE (
    GENERATESERIES(1,48),
    VAR inc = [Value]
  RETURN ROW (
    "Date", DATE(2017,inc,1) ,
    "Month End", EOMONTH( DATE(2017,inc,1), 0)
    )
  )
```
We have added a new column to note the end of each month.

Date Table with Start and End Columns

Well, thanks for following along. In my use case this start of month date table was exactly what I needed. I thought this was a handy little DAX table, and I hope you have found this helpful as well. Be sure to share this post if you found this helpful.
December 20, 2017
Custom Usage Metrics Reporting
One of the really cool features contained within the PowerBI.com service is the ability to monitor how often your dashboard or report is being viewed. You’ll find this feature by opening up either a Dashboard or a Report, then clicking the button called Usage Metrics. Clicking this button will generate a custom usage report. For more details on Report Usage Metrics see the following article from Microsoft.

Note: In order to see the usage metrics report you must be able to edit the report and have a minimum of a Power BI Pro license. Also, the usage metric report only captures the last 90 days of report usage.

The sad thing is that this report is only the usage metrics for the Dashboard or Report that you opened. This report is also read only and cannot be modified. However, there is a way to fix this, and see all the usage on all the Dashboards or Reports within a workspace.

Let us begin.

You will first need to log into PowerBI.com, once you have logged in navigate to either a Dashboard or Report. To open a dashboard or report start by opening a workspace in the left navigation bar. If you are already in a workspace you can open a Report or Dashboard by clicking on the Dashboards or Reports headers in the main selection area of the workspace.

Navigate to a Dashboard or Report

For this tutorial I will use a report but the same steps will work for both the Dashboard and Report usage metric reports. Open up the report that your interested in viewing the metrics. I am using my report called Home for this example, this report is used on PowerBI.Tips, and you can view the report here if your interested. Now that we have opened the report, click on the Usage Metrics link on the top navigation bar. This will open up a usage metrics report. This report is read only and does not allow changes.

Link to Report Metrics

Here is where we get sneaky… If you observe the URL for the usage metrics report it looks like the following:
```
https://app.powerbi.com/groups/me/reports/c6....26/ReportSection?filter=Reports~2FReportGuid%20eq%20%27....ca%27
```
Notice the middle of the report where it states “?filter=Reports” this means the Usage Metrics report has been filtered for only one report in the workspace. The report filter context is passing the Globally Unique Identifier (GUID) of the selected report down to the Report Metrics. This is good news because knowing this we can modify the report and remove the filter, thus allowing visibility to all the reports in a workspace.

First we will need to save a copy of the report so we can make changes. With the Usage Metrics report open click File then in the drop down click Save As.

Save Report As

This will save a copy of the report into the workspace. Notice we now see in the Power BI header bar that the report has been named Report Usage Metrics Report – Copy. Also we can now see an Edit report button.

Usage Metrics Report – Copy

Click on Edit report to start changing the report. As soon as you open the report you can see in the visualizations pane that there is a Report filter applied. Remove this filter by Clicking the little Grey X for the ReportGUID in the Report level filters section of the visualizations pane.

Report Filters

After removing the report filters, we can see all the data from all reports.

Let the modifications begin.

On the left of the report we have a report page slicer. This allows you to see the activity on one page of a report. Now that we can see all the reports across the workspace filtering only pages of each report doesn’t make sense. We need to add an additional slicer to select reports we are interested in.

Report Page Slicer

Select the Report Page Slicer the using Ctrl C copy the slicer, then paste it using Ctrl V. We should now have two slicers on the page. Select the top slicer named Report page.  Change the field of the top slicer from ReportPage to DisplayName. The DisplayName is found under the Reports table in the Fields pane.

Link Report Display Name

Notice even though we changed the slicer information that the slicer title did not change. We have to manually change the title description in the display settings. Click the Paint Roller to open the display settings. Expand the Title section and change the Title Text to Report Name.

Change Slicer to Report Name

The title of the slicer visuals is now changed. Sweet!

Let’s move on to modify some of the standard visuals. Select the report titled Views per day and change it to the following settings:

Views Per Day Change

Note: We changed the Axis Date column field. We removed the date field from the views table and added the date from the Dates table.

This allows us to see over time the number of views per report. Lets clean this up a bit. Change the settings of this visual by Clicking on the Paint Roller ribbon. Start by Turning Off the Legend, then open up the Data Colors, and Click the Revert to default to return the visual to it’s normal colors.

Change Visual Settings

Now, lets modify the Unique viewers per day.

Change Unique Viewers per Day

Again, the formatting of this visual isn’t great so let’s modify it.  Click on the Paint Roller again and Turning Off the Legend, then open up the Data Colors, Click the Revert to default to return the visual to it’s normal colors. Finally, change the visual type from Stacked Column Chart to the Ribbon Chart.  Your visual should look like the following:

Change Visual

This visual will show you which report has largest viewing audience.

You might have noticed that in both of these visuals I’ve been removing the legends. Which means, you don’t know which report is represented by each color. We will fix that by adding a final visual.  Add the Bar Chart visual from the visualization pane. Add the following field names to the visual, as shown below:

Add Bar Chart

Next, we will format the visual to clean it up. Make the following changes, Toggle the Legend to Off, Toggle the X-Axis to Off, Toggle the Data labels to On, Toggle the Title to On and change the Title Text to Report Views, finally change the Font color to Black and Text Size to 14.

Formatting the Bar Chart

We want to sort the reports not by name but by how often they are viewed. To do this, Click on the ellipsis and from the drop down Select sort by ViewsCount.

Now we have a custom Usage Metrics Report.

Final Report

Be sure to save the file. Click on File then in the drop down Click the Save button.

Save Changed Report

You will notice that our modified Usage Metrics Report will be saved in the Workspace.

Thanks for following along. If you found this tutorial helpful please share it with someone who will find this valuable.
November 24, 2017
Power BI Connections: Import
Power BI’s default connection type is Import. In fact, if you have never dealt with a data source that handles multiple loading methods, you may never know that there are different loading methods because Power BI automatically connects via import. However, if you’ve ever worked with sourcing information from databases or models, then you have seen the option to select Import vs. Direct Query or Live Connection.

Note: This is a continuation of the Power BI Connections series. If you would like to read the overview of all the Power BI Connection types you can do so here.

Below is a quick chart to outline some of the considerations to help you decide whether import is right for you.

Connection Type Outline

Import is the only connection type that brings to bear the full capabilities of the Power BI Desktop. As you move from Import to Direct Query to Live Connection, you trade off ease of use for solutions that will scale.

Import will pull in the data from the data sources that you have connected to and store & compress the data within the PBIX file. The eventual publishing of the PBIX file will push the data to Azure services supported in the Power BI Backend. For more information on data movement and storage see the Power BI Security Whitepaper.

When using import, the full Edit Queries suite is available to mash up any data source, transform data-sets and manipulate the data in any way you see fit.

Query Editor

Once you click Close & Apply, the data is loaded into the “front end” of Power BI into the Vertipaq engine.

Note: The Vertipaq engine is used in both Excel and SQL Server Analysis Services Tabular models. In simple terms, it is the backbone that compresses all your data to make it perform extremely fast when visualizing, and slicing & dicing. For more detailed information on the engine see an excerpt from Marco Russo & Alberto Ferrari’s book “The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI” found here.

At this point it is ready for you to extend by building out the relationships between your objects in the model section. After the model is set up you will now be able to add any additional calculations in the DAX (Data Analysis Expressions) formula language. There are two types expressions that you can create, measures and calculated columns. To create these, you can go to modeling, and select the option. When you do this, the formula bar will display. You can also right click on any column or field and select “New measure” or “New column” from those drop down lists.

New DAX Measure or Column

Other than the formula bar with intelli-sense, there are several built in tools that can help you build those calculations.

The first method is to Right Click on the desired field and select an implicit calculation from the drop down:

Using Implicit Calculations

The second is Quick Measures. This can be accessed by using right click as described above.

Using Quick Measures

Here is an example of the Quick Measure dialog box:

Quick Measure Dialog Box

Quick Measures allows you to choose from a wide variety of calculations to generate a measure. Once the measure is created, you can interrogate the measure and see the code that was generated. Click on the measure (denoted by a little calculator next to the text) created by the Quick Measure dialog box to see the DAX code.

Here is an example of what that looks like:

Sample of Quick Measure

This is a great method to get your feet wet while you’re learning DAX.

Note: there are a lot of safety features added to these Quick Measures, such as, an “if” statement wrapped in a “isfiltered”. You might have to remove these bits of code in order to play with the measure.

When you have completed your report and publish the report & corresponding dataset to the Power BI Service, you will need to schedule a refresh. This will be required for any report which relies on the Import Connection. There are numerous use cases that surround whether or not you need a gateway, but a simple rule applies. If the data comes from an on-premises source, you will need one, for cloud sources you usually do not, but you can find in depth refresh documentation here.

The Import connection has the least amount of restrictions between the three methods, Import, Direct Query, and Live Connection. However, there are a few Import restrictions you should be aware of.

First, depending on your data source and the size of the data set, the processing of the model could take a bit of time.

Second, since all the data is being loaded into a table, there is a limitation on how big the file can get for successful publishing to the Power BI Service. That limit is 1 GB for free users & Power BI pro users, 2 GB for Report Server Reports and for Premium Users the size is only bound by the amount of memory you have purchased.

Note: The PBIX file can get as large as you want, however, it won’t let you publish.

Using Import is good when:
1. You can schedule your data to refresh
2. Data only needs to be refreshed periodically
  1. Can be refreshed up to 8 scheduled refreshes in a day (restriction from Power BI Service)
3. The amount of data your importing is relatively small (doesn’t need to scale)
4. You need to mash up multiple sources such as Azure SQL database and google analytics data sources
In summary, the Import method is the most flexible, provides all the tools to connect, mashup, extend and visualize your datasets within the Power BI Desktop. It is likely the most used connection type and is the default for all connections. The data you connect to is drawn in, and a copy created and used in both the Desktop and the Service. Scheduled refresh is a requirement for almost all scenarios, and it is likely a gateway is required as well if your data is not located in the cloud.
November 16, 2017
Fixing the Truncating Bar Chart

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.

November 14, 2017
Creating A DAX Calendar
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.
1. The CALENDAR DAX function generates a table with a list of dates from Jan 1 to Dec 31 of 2017.
2. We define variables (denoted by VAR) to capture details from the column named [Date] that is created by the CALENDAR function.
3. 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:
```
Dates 2 = ADDCOLUMNS(
  CALENDAR( DATE( 2017, 1, 1) , DATE(2017, 12, 31) ), 
  "day", DAY([Date]), 
  "month", MONTH([Date]), 
  "year", YEAR([Date])
  )
```
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:
```
Total Sales = SUM( 'Sample Sales'[Sales] )
```
and
```
Last Week Sales = CALCULATE( [Total Sales],  ALL('Dates 5'),  'Dates 5'[week index] = -1 )
```
If you want to calculate something crazy like the last 5 weeks of sales you can calculate the following:
```
Last 5 Weeks Sales = CALCULATE( [Total Sales], ALL( 'Dates 5' ),  AND( 'Dates 5'[week index]  <= -1,  'Dates 5'[week index] >= -5 ) )
```
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.
```
Moving Last Week Sales = 
VAR filterTime = SELECTEDVALUE('Dates 5'[week index], BLANK())
RETURN CALCULATE( [Total Sales],  ALL( 'Dates 5'[Date] ), 'Dates 5'[week index] = filterTime - 1 )
```
Same goes for a moving sum of the last five weeks of sales.
```
Moving Last 5 Weeks Sales = 
VAR filterTime = SELECTEDVALUE('Dates 5'[week index], BLANK())
RETURN CALCULATE([Total Sales], ALL('Dates 5'[Date]), AND( 'Dates 5'[week index] <= filterTime -1, 'Dates 5'[week index] >= filterTime -5 ) )
```
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

[products limit = “1” columns=”4″ id=”12754″ ]
November 1, 2017
Power BI Connection Types

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 2^nd 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 3^rd 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.

October 16, 2017
Ranking Values with Measures
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.

Total Sales ALLSELECTED = CALCULATE( sum( ‘Clothing Sales'[Sales] ) , ALLSELECTED( ‘Clothing Sales’ ) )

Add this new measure into our existing table.

AllSelected Filter Context

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.

Create a new measure and add the following:
```
Ranking = RANKX( ALLSELECTED( 'Clothing Sales'[Category] ) , CALCULATE( SUM( 'Clothing Sales'[Sales] ) ) )
```
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.
July 25, 2017
Use Multiple Connections Between Tables
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 specific example was based of the article from Marco Russo at SQLBI on UserRelationship in Calculated Columns. Follow Marco at http://www.sqlbi.com/articles/
July 5, 2017
UseRelationship DAX Tutorial – Curbal

Here is another great tutorial from Curbal. This tutorial teaches you how to use the DAX function UserRelationship. This is important when your dealing with a tables in your data model that have multiple relationships. Power BI by design can only handle one relationship between two different tables. This will also be an important function to use when you have source tables that contain multiple date columns. This is a pretty slick function when handling supply chain type data sets.

For the full documentation from Microsoft visit this page.

The Highlights:

Full video below:

Curbal has been generating a lot of great content. To learn about for more information you can visit the website found here, or visit the YouTube Channel.

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

July 3, 2017