@@rcb3921 yeah, it's intimidating because iirc every measure which is based on a relationship in dax is basically translated into a query that has treatas in it, dax doesn't understand relationships it's just translates every measure you write into a treatas statement.
Thank you Alberto, it’s great to know TREATAS can be used for this, and supports multiple columns. Especially helpful when we don’t have a relationship between the tables established. Thanks!
Yes, it's very common in queries returned by performance analyser, quick measures.. etc., But still not so common among DAX users, not many people use it at least from what I see. The SQLBI blog about treatas is a wonderful article that describes it's efficiency. Appreciate it. Thank you.👍
Wooow really I'm feeling great to subscribe the channel and learning DAX in easy understand manner. Thanks for your effort to share your knowledge 📚 to everyone.
Thank you. I think the first example is good to demonstrate the general purpose of the function, but it is the second one that highlights the most appropriate use case - it is "IN" functionality but for multiple columns at the same time. Very valuable lesson!
In reality IN is a simple example, but the IN syntax is good especially for one or just a few values. TREATAS is handy in more complex situations or when you manage larger lists of values to filter.
i don't get it. Why would you use treat as when then relationship works fine? And if you don't have a relationship, why not create one. I am sure this is very powerful, but this example isn't helping me understand
It is part of the companion content of our book - the book is on sale, the companion content can be downloaded here: www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/
I want to filter one table (FactOpenItems[OpenValue_Open] with one column OR relationship between the other two tables (live DimSalesProcess[ProcessID], live DimPaymentInfo[PaymentType]. Can it be used for this as well? The two filter tables are related to the fact table.
Hi alberto, is there any way i can make my report calculate ytd, mtd and ,qtd and wtd values from financial year considering 01-apr as the starting month of the year and 31-mar of next year as end month of the year...??
There is an additional parameter in standard time intelligence functions, but you cannot use them for week calculations. See www.daxpatterns.com/time-patterns/ for all the possible combinations.
Hi Alberto, Thank you for your sharing. I have difficulty to understand the following situation. I wonder if you have help me out. At 0:11, we can see the Sales 2007-2008 is always 21,237,259.11 for each row and this is expected. Note: This is because the context Year Number from the CALCULATE overwrites the Year Number from the Row Context if I understand correctly. I understand I can user KEEPFILTERS if I don't want 21M (total sales for 2007 and 2008) for each row and I want Sales for each individual year. Year Number Sales Amount Sales 2007-2008 -------------------- ----------------------- --------------------------- 2007 11,309,946.12 21,237,529.11 2008 9,927,582.99 21,237,529.11 2009 9,353,814.87 21,237,529.11 -------------------- ---------------------- ----------------------- Total 30,591,343.98 21,237,529.11 where Sales 2007-2008 = CALCULATE( [Sales Amount], 'Date'[Year Number] IN { 2007, 2008 } ) However, if I create the similar table using "Calendar Year" instead of "Year Number" then I expect the same behaviors but the Sales CY 2007-2008 is not 21,237,259.11 for each row. Do you know why? Calendar Year Sales Amount Sales CY 2007-2008 ---------------------- ---------------------- -------------------------- CY 2007 11,309,946.12 11,309,946.12 (expect 21,237,529.11) CY 2008 9,927,582.99 9,927,582.99 (expect 21,237,529.11) CY 2009 9,353,814.87 (expect 21,237,529.11)
---------------------- ----------------------- ------------------------- Total 30,591,343.98 21,237,529.11 Where Sales CY 2007-2008 = CALCULATE( [Sales Amount], 'Date'[Calendar Year] IN { "CY 2007", "CY 2008" } ) Note: The only difference between the two tables is "Year Number" which is defined as numbers and "Calendar Year which is defined as characters. Please note that similar behavior occurs for Fiscal Year. Thanks, Lawrence
Hands down ...
When I watch these videos it is like programming poetry. What level!!!!
Thanks
This whole series is well worth periodic re-watching.
TREATAS function always looked strange to me and was never able to understand it until this Video. Thank you Alberto and SQLBI :)
I agree. Something about it is intimidating. This video really demystifies what's going on.
@@rcb3921 yeah, it's intimidating because iirc every measure which is based on a relationship in dax is basically translated into a query that has treatas in it, dax doesn't understand relationships it's just translates every measure you write into a treatas statement.
These videos are incredibly useful! Thanks for sharing your knowledge :)
Thank you Alberto, it’s great to know TREATAS can be used for this, and supports multiple columns. Especially helpful when we don’t have a relationship between the tables established. Thanks!
but why don't you just create the needed relationship???
The best explanation in my opinion for users with intermediate knowledge.
Thanks, Alberto! Hugs from Brazil.
You guys always blows my mind!
Never used treatas before, but I can see now how useful this is thanks to your great explanation. Thanks again!
Yes, it's very common in queries returned by performance analyser, quick measures.. etc., But still not so common among DAX users, not many people use it at least from what I see. The SQLBI blog about treatas is a wonderful article that describes it's efficiency. Appreciate it. Thank you.👍
Great! i use your tips to sovle the self-defined date calendar calcuation.
Really Amazing！
Beautiful function!
Wooow really I'm feeling great to subscribe the channel and learning DAX in easy understand manner.
Thanks for your effort to share your knowledge 📚 to everyone.
Wow! Thanks Alberto ! With this I will be able to solve a scenario.
Have seen Treatas in DAX Studio, was not clear about the semantics behind. Thanks SQLBI ! DAX Master.
you are sharing awesome knowledge
Thank you. I think the first example is good to demonstrate the general purpose of the function, but it is the second one that highlights the most appropriate use case - it is "IN" functionality but for multiple columns at the same time. Very valuable lesson!
In reality IN is a simple example, but the IN syntax is good especially for one or just a few values. TREATAS is handy in more complex situations or when you manage larger lists of values to filter.
Really Helpful...Thank you!
Amazing ! Really Helpful...Thanks Alberto
Thanks in a million. Great content. Awesome. Grade: A++💥
Thanks so much! Much clearer I was stuck with this function in your book, so this helped a lot. Enjoy DAX ;-)
Waoo Amazing Explanation. Thanks Alberto , I really enjoy Dax!
Amazing!!!! Mind-blowing as always!!!
Wooowww it's Amazing... Thank you soomuch...
Amazing! Thanks for this great video that explains the TREATAS so well.
So grateful !!!! thanks!!!!! molte grazie!!!! grazie infinitamente!!!! GRACIAS !!!!! great work
i don't get it. Why would you use treat as when then relationship works fine? And if you don't have a relationship, why not create one. I am sure this is very powerful, but this example isn't helping me understand
Amazing explanation like always!
Is there a way to use TREATAS with the selected value in the slicer to filter a table?
The selection in a slicer can be read using VALUES, but I'm not sure about what you want to achieve with TREATAS.
Crazy, how you make the TREATAS so easy to understand?
Can the database you use for your video’s be downloaded somewhere? Its a Nice dataset for practise. Nice video
It is part of the companion content of our book - the book is on sale, the companion content can be downloaded here:
www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/
wonderful information like always
Wonderful video.
Finished watching
Very Interesting!
Is it more performant than using the "in" statement you used earlier?
Not much for few elements, it could be better if the number of elements is large. As usual, you should check on your model and your specific case!
This is great, thanks a lot!
I want to filter one table (FactOpenItems[OpenValue_Open] with one column OR relationship between the other two tables (live DimSalesProcess[ProcessID], live DimPaymentInfo[PaymentType]. Can it be used for this as well? The two filter tables are related to the fact table.
Hi alberto, is there any way i can make my report calculate ytd, mtd and ,qtd and wtd values from financial year considering 01-apr as the starting month of the year and 31-mar of next year as end month of the year...??
There is an additional parameter in standard time intelligence functions, but you cannot use them for week calculations. See www.daxpatterns.com/time-patterns/ for all the possible combinations.
@@SQLBI many thanks for the reply, will check the link surely
Great information!!! In terms of performance, how does TREATAS compare to using "FILTER" or "CALCULATETABLE"?
I guess that Power BI internally translates all the relationships into respective TREATAS functions when building queries, isn't it?
No, technically it works in a different way, but details about the query plan could change over time so don't rely too much on them.
In terms of performance, how faster (%) can TREATAS be when compared to "IN" approach?
It depends, it could be relevant for large sets. Test the performance on your model and your specific case!
When the formula had only year I was like meh... then it was used year+month and I was like WHAAAAAAATT
Hi Alberto, Thank you for your sharing.
I have difficulty to understand the following situation. I wonder if you have help me out.
At 0:11, we can see the Sales 2007-2008 is always 21,237,259.11 for each row and this is expected.
Note: This is because the context Year Number from the CALCULATE overwrites the Year Number from the Row Context if I understand correctly.
I understand I can user KEEPFILTERS if I don't want 21M (total sales for 2007 and 2008) for each row and I want Sales for each individual year.
Year Number Sales Amount Sales 2007-2008
-------------------- ----------------------- ---------------------------
2007 11,309,946.12 21,237,529.11
2008 9,927,582.99 21,237,529.11
2009 9,353,814.87 21,237,529.11
-------------------- ---------------------- -----------------------
Total 30,591,343.98 21,237,529.11
where
Sales 2007-2008 =
CALCULATE(
[Sales Amount],
'Date'[Year Number] IN { 2007, 2008 }
)
However, if I create the similar table using "Calendar Year" instead of "Year Number"
then I expect the same behaviors but the Sales CY 2007-2008 is not 21,237,259.11 for each row. Do you know why?
Calendar Year Sales Amount Sales CY 2007-2008
---------------------- ---------------------- --------------------------
CY 2007 11,309,946.12 11,309,946.12 (expect 21,237,529.11)
CY 2008 9,927,582.99 9,927,582.99 (expect 21,237,529.11)
CY 2009 9,353,814.87 (expect 21,237,529.11)
---------------------- ----------------------- -------------------------
Total 30,591,343.98 21,237,529.11
Where
Sales CY 2007-2008 =
CALCULATE(
[Sales Amount],
'Date'[Calendar Year] IN { "CY 2007", "CY 2008" }
)
Note: The only difference between the two tables is "Year Number" which is defined as numbers and "Calendar Year which is defined as characters.
Please note that similar behavior occurs for Fiscal Year.
Thanks,
Lawrence