Building a Virtual Relationship in Power BI Basics of TREATAS DAX Function

hello this is Reza from Radek ad and in this video I'm going to talk about function index called treat as which you can use it to filter values which you can use it for creating virtual relationship between tables in this video I'm talking about basics of that function let's see how it is best way to learn treat as is to use an example so here I have a data model which you can download it from the link down in the description below to my blog article you can download the file from there I have a sales table customer table date table they are not related to each other now one thing to note at the start is that it is not recommended to leave tables like this without relationships I definitely recommend against it I recommend to create relationship between tables relationships means filtering and everything in your model would be filtered that way however for this example intentionally I removed the relationship to show you how the treat has works if I don't have a relationship and then I visualize something like this English education from the customer table to be filtered by sales amount which comes from fact table you see the sales amount is not filtered by English education because there's no relationship now using three tasks you can change that behaviors here I have I have a measure let me show you the expression for that measure I have a measure and in this measure I am saying that calculates some of the sales amount so far it is just calculate sum of sales but here I say treat as values of customer key from team customer and then by customer key in fact internet sells now let's talk about how the treat as works actually treat as is a function that gets a few parameters to it as is a function that get a few parameters here you can see the kind of signature of treat as it gets an expression and then column 1 column 2 column 3 the expression should be an expression that returns a table it's a table expression that is one of the reasons why you see values here because combination of these two returns a table values of M customer customer key that means unique list of customer key and you might use other things like all or you might use let's say select columns or anything else right or distinct the different value the result value would not be exactly the same but but any function that returns a table would be you would be able to be used here right that would be the expression and then column 1 column 2 column 3 and they should be in the same order that this table returns so same order of columns here same order of columns over there now we are talking about basics for now let's see what is the result of this expression if I use this in my table you see that this gets filtered even though I don't have a relationship between these two tables you see these two tables they don't have any relationships but this calculation works perfectly fine now how the treat has works you should read through it as like this you should say treat this the first parameter as this the second parameter so you are saying that treat customer key from dim customer as the customer key in fact internet sells that means if this is filtered filter that one if we are looking at customer key 1 2 3 this should be also customer key 1 2 3 now because in our visual in our visual English education is filtered as a result that filters customer key because English education and customer key they both are in the same team customer table that would filter customer key and because in treat as we said customer key of team customer filters the customer key of fact internet sales treat this as that so as a result this will that it's like creating a virtual relationship between these two tables and remember the reason I we used values is that this should return a table because if you just say this column like that column you will get an error that says here we expect a table expression not a single column name so you cannot just use column name like that you can use it inside values you can use it inside distinct they both would be the same you can even use things like let's say select columns from dim customer you can call this column X or whatever and return this and then use that all of these are possible options and they would all work perfectly fine but for this example values would be let's say one of the simplest way of using it which is why I used it here right and it returns the result that I want so without having the relationship is created a way of filtering there is another way to use to it as well and you because this is a table you can replace this with a scalar table hmm function something like a table constructor in another blog article I explained what a table constructor is let me show you here that as an example so here I have another measure that inside that I use a treat as in this way so my table is just like this inside these curly brackets I have high school this is a table with one single row one single column it's a table constructor these curly brackets are table constructors if you want more information about that look at the link down in the description below from my blog post you'll find it this is saying that consider this as that value so basically it is saying that team customer English education should be filtered by high school right and then give me count of rows for that you can use treat as like this another example of using through it as is role-playing dimension let's say I have fact internet sales that I want sometimes to be filtered by order date sometimes to be filtered by ship date this is what we call role-playing dimension in bi scenarios and you have two ways to create that one way is to create a copy of your date table one call it order date one call a cheap date create active relationship another is to create one active and one inactive relationship and use use relationship one other way is this you can use treat as in this way so here you can see that I used a couple of measures this is for order date which is calculate sum of order quantity but then treat as that date key as they ordered a key the other one is saying that treat that as a ship date date key that is the only difference as a result here I have one by order date one by ship date these are quantity of orders partner order date is in 2005 but this is those that their ship date is in 2005 right but that's how you can read this visual so without creating relationship I use this approach to create relationships now I'm not saying that this is best practice to do it this way this is because this would raise some ambiguity like what is the relationship and things like that you have to look at the expression to understand it but I use these examples to explain how treat as works the main thing learning about rita's is that you should read it this way treat this as that and your first parameter is a table now in the next video I'm going to talk about what if that table is multiple fields because you can then relate two tables based on multiple fields which is an interesting case of using three dots if you liked this video go ahead and subscribe into our youtube channel we have weekly videos of power bi anyway [Music]

Channel: RADACAD

Views: 9,601

Keywords: Power BI, DAX, Calculation, TreatAs, Relationship, RADACAD, Power BI from Rookie to Rock Star

Id: YCJjeD8wz5c

Length: 9min 1sec (541 seconds)

Published: Wed Mar 04 2020