Monthly Archives

2 Articles

Power BI Pro Tip: Role playing dimensions with UseRelationship

One frequent need in BI is something called a role playing dimension (see http://en.wikipedia.org/wiki/Dimension_(data_warehouse)#Role-playing_dimension for a definition). The classic example of this is a Date dimension that is related to a Sales fact table and used either as date of order and date of shipping. One obvious solution would be to add the date table twice to your data model, but that would not only result in a larger data model but also in more work: if you add a calculated column to the date table (such as to use the sort by column trick I explained earlier) you would have to do that on every single date dimension in your model.

Luckily Power Pivot has a handy function called USERELATIONSHIP (explained here: http://technet.microsoft.com/en-us/library/hh230952.aspx)). In this post I will walk you through an example.

Let’s say you have this simple data model again:

This is a Date dimension related to a Sales fact, just like in my last Power BI pro tip.

The relationship between the two tables is on the Order Date:

Now, notice that the Sales table also has a column for Shipping Date. Let’s create a relationship based on that column to the Date table. You can either drag and drop the columns or right click on ShippingDate and choose ‘Create Relationship’.

Here are the settings I made:

 

When you hit create you end up with two relationships between Sales and Date as expected, but one is a dotted line, which means it is inactive. This will be the relationship on shipping date.

No, create a report on this where you display SalesAmount per Month, like this:

The question now is which Date we are looking at. Is it Order Date or Shipping Date? It turns out that the active relationships is used, which is the one on order date.

Now, I would like to add a graph that shows the sales amount by shipping date. For this we will need to add a measure to our data model. Go to Power Pivot and add a measure to your Sales table by selecting a field in the bottom part of your Power Pivot window:

In the formula bar enter the following formula: SalesAmountByShippingDate:=CALCULATE(SUM(Sales[SalesAmount]);USERELATIONSHIP(Sales[ShippingDate];’Date'[Date]))

(Note that you might need to use comma’s instead of semi colons to separate parameters).

Now add another graph to your Power View report where you use this measure and plot that against months:

Now the graph on the right shows the sales amount by month using the shipping date as the relationship, while the graph on the left still shows the sales amount by month using the active relationship, which is order date.

And that’s how you use USERELATIONSHIP to show data when using a role playing dimension in Power BI.

That’s it for this Power BI Pro Tip. Until next time!

Power BI Pro Tip: Sort By Column

Here is an easy solution to a very common problem when making reports: how do I change the sort of some items from alphabetically to something else?

The most apparent sample of this is Months. Let’s say you have to following (very simple) data model in Power Pivot:

That is, you have a Sales table that reports SalesAmount on a Date and related to that Sales table is a Date table (dimension) which stores dates and month name.

Now you create a PivotTable and provide a slicer to filter:

Or you create a Power View report:

What’s wrong here? Your users probably want the months ordered correctly not alphabetically by month name as they are now in both the slicer and the Power View graph.

Of course you can give the months a numeric prefix like ’01 – January’, ’02 – February’, etc. This may work perfectly fine for you but I think this approach is impacting the user interface to much.

There is, naturally, a better way. And it is very easy to implement. All you need is two modifications to the date table.

  1. In Power Pivot go to the date table and add a calculated column with the following formula: =Month([Date]) :
  2. Then click on the column that contains the Monthname (my second column) and choose Sort By Column:
  3. In the dialog choose MonthNumber as your column to sort by and click OK:
  4. Done.

 

Now go back to Excel and look at the slicer and your Power View report. Both of them now sort correctly thanks to the power of Sort by Column. This of course is applicable to anything, not just months or dates.

 

That’s it for this Power BI Pro Tip. Until next time!

%d bloggers like this: