- Ciao friends. In this video, I want to show you that you can use the TREATAS
function in CALCULATE whenever you work with tables. Let's start with our goal. I have already a measure
here that computes the sales amount, where the
year is in 2007 or 2008. This measure, replaces the filter context in the year with a new filter context containing 2007 and 2008. And you can see the result here. Despite any year I put in the rows the total is always the sum
of 2007 and 2008 together. It is very unlikely that
you are going to use the measure with the year. So, we can get rid of the year from here. And instead we use, for
example, the product brand. Now it's a bit more interesting. I'm looking at the brand,
the full sales amount and the sales in only two years. Now, my measure right now
is using the IN function. There is another way of
obtaining the same goal, which is very commonly used by Power BI that is using the TREATAS function. Here, under curly brackets, I'm creating a temporary
table containing two numbers. If I just remove date year
number from the expression, and I only use the table as it is, from a syntactical point of view, it works but it doesn't filter anything. You see that the total
here now is the grand total because this table is not tied
to any column in the model. So the engine has no
clue on what to filter with the two numbers 2007 and 2008. There is a function
TREATAS that takes a table as its first argument
and tells the engine, which column that table needs to filter. So we can use TREATAS, and we are saying, treat the
table containing 2007 or 2008 as if it were a date year number column. So, now the engine will
know that 2007 and 2008 are indeed numbers that need to be used to filter the year number. And using this syntax I
have now the same behavior I had before. There is more about TREATAS. What if I'm not interested in two years, I'm interested in two months
in two different years. For example, I might want to see December, 2007 and January, 2008. Now what I need is a table
that contains two columns, the year number and the month number. So, the table will no
longer contain only these, but it will be different, it will be 2007, 12 and then 2008, 1. What I'm creating here, is
a table containing two rows. The first row has two columns 2007, 12. The second row has two
columns, 2008 and one. And I need to tell the engine,
the first column is a year, the second column is a month. TREATAS can do that. I can tell the engine
that the first column, the second argument of TREATAS is the type of the first column, but I can also say that the second column
is a date month number. Let me format the code a bit
better so we can read it. I have the table, then I have
a type of the first column and the type of the second column. Now, it's clear that year number is 2007 month number is 2008. And I after I hit Enter,
now the numbers will change reflecting the sales in only two months of two different years. TREATAS is very powerful. You will find a lot of
different TREATAS usages if you inspect the queries
generated by Power BI, because it's a very
well optimized function that proves extremely useful. Enjoy DAX!
