PowerBI.tips

Tag: DAX Variables

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
Using Variables within DAX
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 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}}),
 #"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.
1. Create the measure
2. Change the formatting of the card
3. 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:
May 5, 2017
Measures – Year Over Year Percent Change
This tutorial is a variation on the month to month percent change tutorial. This specific exploration in year over year performance was born out of reviewing my google analytics information. The specific analysis question I am trying to answer is, how did this current month of website visitors compare to the same month last year. For example I want to compare the number of visitors for November 2016 to November 2015. Did I have more users this year in this month or last year? What was my percent changed between the two months?

Here is a sample of the analysis:

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

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

Load Monthly Data

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

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

Change Measure Format

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

Add Stacked Column Chart

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

Change X-Axis

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

Change Colors

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

Change Title

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

Change Month to Whole Number

Another successful percent change tutorial completed. I hope you enjoyed this year over year month comparison example. Make sure you share if you like what you see.
December 5, 2016