Skip to main content

Creating Relationships Using TREATAS

In February 2017 Power BI introduced a new function called TREATAS. It simply creates virtual relationships between tables. But why we should create virtual relationships instead of physical ones? Actually, we shouldn't. It is recommended to use physical relationships whenever it's possible, but Power BI is not always sunshine and rainbows. Especially when we need to create relationships between tables that have different granularities. For this example, I have an advertising budget table for the year 2019 and a calendar table for many years:



In this situation, I need to create a relationship between my budget table and calendar table, so that I can create time intelligence measures. But I can't do it, because these two tables have different levels of granularities. While the calendar has a row for every day, the budget table has a row for every month, which means my calendar has higher granularity than my budget table. If we create relationships between tables that have different granularities, we don't get a direct error, but we get wrong results. First, I have created a relationship between the year columns of both tables and then month columns. Not surprisingly, I got the same wrong result both times.



It's not surprising that I get the wrong results because Power BI doesn't know which month belongs to which year when I have the relationship between month columns. And when I make the relationship between years, it's not able to break the total number into months because there is no relationship between month columns. So what do we do now? We need to use the TREATAS function to solve our problem. Microsoft's definition for the function is: "Applies the result of a table expression as filters to columns from an unrelated table." As it doesn't say much as a definition, here comes my definition: "With this function, first we create a new granularity level which matches each other, and then we create a virtual relationship between tables using that new granularity level." The good thing is that all of these happen virtually and it's very easy to do. Before using the function, let's check the syntax:

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )

And here comes the measure to solve the problem:




First of all, we need to use the CALCULATE function to change the filter context of our budget amount column, where we have the values for each month. Then, for the first parameter of TREATAS function, we create a virtual summary table of our calendar with the columns we need, and for the second part, we do the same thing for our Advertising Budget table by using the SUMMARIZE function. Doing this allows us to match them at the same granularity level so that a virtual relationship can be created between them. This is the table with the measure above:



Problem solved! It's amazing what this function can do and how it can save the day. To make the function work properly, we need to have the same amount of columns in the same order as the first and second parts of the parameter. Lastly, there shouldn't be any physical relationship between the two tables. If we need a physical relationship between them for other reasons, we should create it using the USERELATIONSHIP function.

Comments

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...