Propagating filters using TREATAS in DAX

How to create a virtual relationship in DAX using the TREATAS function. Article and download: sql.bi/41311/?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@DIGITAL_COOKING]

Alberto you're the best teacher of DAX I ever seen

[@Connor-kv5cm]

don't usually comment but wanted to say a huge thanks for making these videos/ blog posts. You're the only channel delving into these abstract but critically important DAX issues. I see myself as an experienced data analyst but learn new and amazing things in your content every time. The thoroughness and the way you simply explain complex issues is just amazing

[@SQLBI]

Thank you!!

[@Turbo_PilotSky]

Hi Alberto, Thank you for your wonderful teaching and the videos that you create.

[@ed2921]

You guys make this stuff look easy. Nice work gents.

[@paulosergiorocha2084]

Very nice! Its always good to have choices on your sleeve.

[@JohnSmith-rx2uv]

This really is one the best PBI videos I've ever seen! Wow I've been struggling to find ways to avoid bi directional relationships Thank you so much Alberto!

[@lorentsnv]

Awesome, thank you Alberto, again and again!! :-) Really appreciate that you share your huge knowledge, in a very consumable way!!

[@dallasyount4072]

Excellent demonstration with examples of best solution based on model

[@madanjoshi5004]

You are amazing Alberto...

[@ShabnamKhan-vk7fj]

This is awesome, thanks so much Alberto!

[@guilhemdelorme9089]

Great explanation of TREATAS, thanks! I actually never did my homework on it and built my models so far with physical relationships that I deactivated when necessary, and leveraged USERELATIONSHIP ... Guess it was not a bad option performance-wise!

[@SQLBI]

USERELATIONSHIP is better than TREATAS - you should use TREATAS when other approaches are not possible.

[@joaquimcosta952]

Very good like always. Thanks.

[@ayushipahwa6497]

🙌🙌

[@Bharath_PBI]

Thank you as always.. 🙂

[@beginho2454]

Magical Function, like you... :)

[@szpyrkowski]

great video

[@kfirbar3928]

Magic !

[@aishwaryajose2997]

Another great video ! I am wondering though if the visual cross filters on a powerbi report would still work in the same manner using these virtual relationships in DAX ? is it not mandatory to have a physical relationship between tables for the powerbi visual cross filters to work ?

[@chakrabmonoj]

Alberto - I watch all your training videos with a lot of interest. In fact most of my dax I have learned from your website and also Marco Russo. The book by you both is never out of sight. I had a question though : Does it matter in which order the tables are linked in these functions - Treatas, Intersect or Contains? E.g. Would the dax still work if I wrote the Treatas code as : Treatas(Values(salesdetail[order number], salesheader[order number]? Does it have to be from the 1-side to the many-side (even though there is no explicit relationship here). I have a M-2-M situation and was wondering if any of these functions would work in that situation?

[@adityaupadhyay3242]

You make DAX look effortless. Best explanation of TREATAS. Do you have any video on ISFILTERED and ISCROSSFILTERED function?

[@SQLBI]

They are certainly explained in our Mastering DAX video course. There is not much to say about those specific functions other than they are related the filter context propagation through relationships and expanded tables (which is the real topic to look for). You can also find useful content on DAX Guide: dax.guide/isfiltered/ dax.guide/iscrossfiltered/ This is the link to Mastering DAX: www.sqlbi.com/p/mastering-dax-video-course/

[@UlverSK]

Thank you!

[@deepakgupta3192]

Hi Alberto thank you for this video, i have one request if you can make video on DATEADD using TREATAS dax , i am referring your Book, The Definitive Guide to DAX , and this DAX function m not so clear as what is happening here, in order to obtain the correct value. if you can explain better, will be a great help. Many thanks , Take care.

[@arm1jo]

oh ALberto, you're god of dax )

[@juancarlosfigueroafigueroa943]

Hi Alberto need your help with a dumb problem? I have an order report published on a PBI app. A person can be first or second seller in an order and the report has RLS over the orders and linked to the seller code. My fact table has separate columns with first and second seller code. I just figured out that they can only see their first sales orders but I had a measure with their second seller orders wich they can't see due to the RLS auto filtering. I need your DAX magic! The second seller measure is a calculate with an ALL over salesman dimension table and a TREATAS with the actual seller code (VAR) to transform it to the second seller code.

[@zxccxz164]

treatas is very confusing. Behind the scenes when you copy query you will see it used all over. But i rarely see it built into normal measures

[@manohara3060]

Hi I am using TREATAS Because I am not having unique value of the master table. Now I need to make filter in Master table. Please advise

[@rashidkhan8161]

Hello, In power Bi, I have 2 excel files Jan-22 & Feb-22, each file contain 5 sheets sales, product , region, date & category, when I get 1 file and transform it all 5 sheets showing 5 queries separately, how can I combine feb-22 file in it and make it dynamically. Thanx

[@Dims94]

Alberto- If I use a variable it doesn't give correct result, also which table should be in summarize, is there any direction of realtion that should be considered? test = var AdvertisingAmount = sum(Advertising[AdvertisingAmount]) VAR treatassales = CALCULATE ( AdvertisingAmount, TREATAS ( SUMMARIZE('Date', 'Date'[YearMonth]), 'Advertising'[YearMonth] ) ) RETURN treatassales

[@ikar2k]

Alberto, thank you very much for the great video! You said it's bad practice to have a separate table with headers. What would you recommend to read or watch on this topic? Thank you in advance!

[@ikar2k]

Yes, I have found it! 👍kzbin.info/www/bejne/iGm2kn2VlLqAfqs

[@sbn4862]

I sae it again today, Treatas was too deficulat to andurstand to me in explanations with time itelegecy as sales previous month.Now i see it's more powerfull function

[@forworkuseonly1388]

Hello, one question, circular dependency is the same as ambiguos? I'm a beginner. Thank!

[@SQLBI]

No, look at the differences in these articles/videos: www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/ www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ www.sqlbi.com/articles/understanding-circular-dependencies/

[@victorrivas7911]

Alberto, but TREATAS() inherits the lineage of both 'Date' and 'Product', wouldn't it create also ambiguity? As if there were physical relationships?. Or using TREATAS() give us the possibility to use both lineage at the same same, avoiding ambiguity?

[@SQLBI]

Look at the answer to a similar question made by Paulo Sergio Rocha. The TREATAS creates a filter using two columns, when you propagate a filter through relationships you filter one column only in the target table and only one path of propagation can be used, not both.

[@steinarbreivik1895]

How do you get from a working code in Dax Studio to a working measure in PowerBI Desktop? Is there another video that shows this? If I try to run a measure in Dax Studio without e.g SUMMARIZECOLUMNS it fails and if I try to run a measure in PowerBI Desktop including SUMMARIZECOLUMNS it fails there, so I'm kinda stuck between Dax Studio and PowerBI Desktop how you convert a working code in Dax Studio to PowerBI desktop

[@SQLBI]

SUMMARIZECOLUMNS cannot be used in measures because it doesn't support context transition. See dax.guide/summarizecolumns/

[@paulosergiorocha2084]

Maestro Alberto, you said that if there was a physical relationship: Promotion[Category] -> Product[Category] Promotion[Year] - Date[Year] ... the Model would be *ambiguous* because there would be two different paths. (Let alone the repetition of the Year in Promotion[Year] - Many-Many) But when creating the Measure and use the Summarized table as a Filter in Calculate, aren't we doing a similar filter and then traversing the same two paths? Can you elaborate on that difference? Thanks!

[@SQLBI]

The goal is to select a Promotion, which includes a specific combination of Year and Category. This would be the desired result if the relationship worked crossing the multiple paths in an AND condition, which is never the case. When there are multiple paths of filter propagation, only one can be used by the engine. Therefore, the SUMMARIZE creates a specific filter over two columns at the same time, producing the required result.

[@paulosergiorocha2084]

@@SQLBI Thanks Maestro!

[@javedkhan-tz6fn]

I just love your videos.. thanks Alberto

[@Wzxxx]

I wish there was a one file/datebase with same data as in all movies on the channel so one can follow all exercises. Files at the end of article are different.

[@SQLBI]

Depending on the video, small adjustments could be made - but we prefer to keep the file of the article as a reference to avoid confusion.

[@BernatAgulloRosello]

So summarize always keeps lineage? It's a bit confusing sometimes to indentify if your lineage is still there or not. It's there any reference to that in dax.guide?

[@SQLBI]

In general, all the DAX functions keep the data lineage, unless the column is the result of a scalar expression. It is only for set functions (UNION/INTERSECT/EXCEPT) that specific conditions apply because each column can be the result of the manipulation of different columns/table. Describing the data lineage behavior for each function would require duplicating the same rules in many function, which would be of limited use.

[@BernatAgulloRosello]

@@SQLBI oh, I see! Thank you. So easy when I see it, so tough when I write it. Enjoying dax 😀

[@EngTarek831]

where can i find the pbix file to try on same data structure ?

[@SQLBI]

Use the link in the description.

[@florentibus01]

Whenever you can't create a physical relationship you revert to a virtual relationship. I've been doing a lot of the latter lately.

[@InnovateTechnology]

Love your videos as always so I have a total different question: what is the make and model of your chair? Regards, Jørgen

[@PeterKontogeorgis]

I was just wondering the same actually. Could be a Herman Miller Aeron, but hard to tell from the small part that’s visible.

[@SQLBI]

Correct - you can find all the details of Alberto's setup here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/

[@yekhtiari]

I have faced a challenge that Treatas inside calculate does not work and needs calculatetable to return correct value.I checked the VALUES ( sales[CurrencyCode] ) and VALUES ( sales[SalesDate] ) are returning one record so context transition happens ,but i cannot understand why i need calculatetable inside Treatas to get the correct result (one row). define var tbl= TOPN ( 1, FILTER ( Sales, sales[SalesDate] > DATE(2022,08,01) ) ) evaluate ADDCOLUMNS ( tbl, "rate (wrong)", CALCULATE ( --SELECTEDVALUE( CurrencyExchange[ExchangeRate],error("more than one rate is provided")) countrows(CurrencyExchange) ,TREATAS ( (VALUES ( sales[CurrencyCode] )), CurrencyExchange[FromCurrency] ) ,TREATAS (( VALUES ( sales[SalesDate] )), CurrencyExchange[PK_Date] ), TREATAS ( { "usd" }, CurrencyExchange[ToCurrency] ) ) ,"rate (correct)", CALCULATE ( --SELECTEDVALUE( CurrencyExchange[ExchangeRate],error("more than one rate is provided")) countrows(CurrencyExchange) ,TREATAS ( calculatetable(VALUES ( sales[CurrencyCode] )), CurrencyExchange[FromCurrency] ) ,TREATAS (calculatetable( VALUES ( sales[SalesDate] )), CurrencyExchange[PK_Date] ), TREATAS ( { "usd" }, CurrencyExchange[ToCurrency] ) ) ) I

[@manohara3060]

=CALCULATETABLE ( SUMMARIZECOLUMNS ( LayoutGrouping[LayoutGrp], "A.Revenue", CALCULATE ( SUM ([BaseAmount1] ), TREATAS(VALUES(LayoutGrouping[AC Code]),SunData[Account Code]) ) ) ) This Query is not working.. please help me out