Filter columns, not tables, in DAX

Why you should always filter columns and never filter tables with CALCULATE in DAX!
Article and download: sql.bi/847199?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...

[@stuartsaint4581]

"Whenever you reference a table in DAX, it is always the expanded table." I see this a lot when somebody starts learning DAX coming from an Excel background, it's a hurdle that people don't necessarily have if you come from a SQL background where this concept is more familar. If you are watching this and feel frustrated to have not known this earlier, remember that you were smart enough to get this far in the first place!

[@leppyr64]

This 100%

[@akshayebenezar]

Do you have a video/ playlist with all the golden rules n best practices. Love your work!! ❤

[@pluu153]

Thank you so much for providing such valuable free content. After using Power BI for six months, I've gained a solid understanding of the concepts you've taught. Your lessons have been instrumental in my professional growth as a power bi data analyst

[@jonoakdale]

I've been waiting for an in depth explanation around this golden rule for a while; thank you!!

[@Nalaka-Wanniarachchi]

Another Fantastic one.Alberto & Marco always opens new horizons in DAX to think over.

[@nishantkumar9570]

Thanks so much for sharing the understanding about the difference in table and column filters and it's impact. Will definitely keep this in mind while writing measures.

[@nevermore17011990]

if there is a nobel for DAX, this guy deserve it !

[@emersonlopez5310]

Great video, thanks a lot. I got the same result by modifying the "Sales Amount" and "Receipts Amount" measures because in sumx I used filtered tables for multiplications greater than or equal to 500, however I am doing a double iteration in each case.

[@gurvindersingharora5301]

Please upload more dax videos related summarize and ADDCOLUMNS together usage

[@mwaltercpa]

Thanks for this insight on expanded tables.

[@gvitullib]

Thank you. A great article/video on this important golden rule!

[@fsanfo]

Thanks for sharing! I think it would be nice to have a video about when the use of filters in tables is unavoidable.

[@SQLBI]

Never?

[@workstuff5253]

Counting with Alberto! Love it!!

[@nikakalichava8012]

Thank you for sharing this. really insightful.

[@rowanschoultz1022]

Alberto, you mentioned that this is one of the "golden rules of DAX". Do you have a compilation of these golden rules available anywhere? See you Sep 18th in Calgary.

[@ajaaskelainen]

Thank you!

[@RobertoStaltari]

SQLBI: Masters at work.

[@anilyadav-rt4sr]

Thanks Sir ji after a long time create a nice video

[@xyclos]

Great explantion, Thanks!

[@marcofestu]

The DAX magister

[@brnnie]

Thank you for sharing this ❤

[@lovlyhearts288]

Nice explanation

[@AlirezaEmamiFard]

Thanks, it's really useful

[@anoopdube9581]

Very insightful

[@dutch-man]

Nice video as usual 😎👏

[@stylianoschiotis5191]

Excellent 👌

[@paulinafaryna6372]

This is brilliant

[@mogarrett3045]

thank you

[@common_sense4753]

@sqlbi thanks. I just got confused, though. I always thought that what you described as the column filter was ultimately converted by the engine to filter(all(sales), amount>=500) Now I’m confused :)

[@SQLBI]

It is converted to a column filter, not to a table filter: www.sqlbi.com/articles/filter-arguments-in-calculate/

[@Nalaka-Wanniarachchi]

@@SQLBI Ah,That means FILTER(ALL(Sales[Quantity],Sales[Net Price]), Sales[Quantity] * Sales[Net Price] >= 500) etc..?

[@gnico64]

@@Nalaka-Wanniarachchimy question exactly! Need to try it on a model to make sure it works like this or not

[@louiseshorten9389]

I didn’t know about the golden rules. Thanks for this video! Just wondering, I assume you could add a Boolean flag as a Column which checks whether the relevant values >500 and then this could be the filter. How would this compare? Thank you

[@SQLBI]

Yes, see this article for the fundamentals: www.sqlbi.com/articles/filter-arguments-in-calculate/

[@attaurrahman1880]

Thank you for Sir

[@marcomapelli5953]

Molto interessante! C'è un elenco di queste "regole fondamentali" da qualche parte?

[@gurvindersingharora5301]

Hello Alberto Sir, A lot of people are creating Pareto Chart in Power Bi Using various techniques of DAX. What will be yours ?? Please Create a Video of it !! ❤

[@SQLBI]

You have a full pattern here: www.daxpatterns.com/abc-classification/

[@douglascory]

I always found it weird when people used FILTER inside a calculate, like I always thought: There must be a reason they are using it, since calculate already filters Nope, turns out it was just wrong lol

[@pillslifestylereviews6714]

The screenshot perfectly describes me when DAXing...

[@workstuff5253]

Slightly off the topic (but still related). I suspect some of the reasons are based on requirements to enable a better demonstration, but is there an advantage to having DAX calculate the sales value (net price * quantity) over having the value calculated in an ETL process and having the value stored in the model as a further column.

[@SQLBI]

Usually you save memory thanks to a better compression, unless you have billions (not millions) of rows, in which case the difference is negligible and the perf improvement of a single column is relevant (it is not with millions of rows).

[@Superninja1211]

Billion !!!... not Million !!!.... But other than that thanks. Very helpful!

[@amitsatnalika5760]

this problem of intersecting expanded table arises bcz we have two fact tables here and we are using these two together inside a single calculate, we can use these in two separate calculate and add those measure............................................................................................ what if if we have only one fact table , there expanded tables helps , let say in case where we have a measure associated with a dimension table and we need to respect filter coming from other dimension table , in that case only table filter or expanded table is the rescue.

[@SQLBI]

Normal filter propagation in that case doesn't require the expanded table. The example in the video is a simplification of a more complex real-world case scenario where table filters are applied on high-level measures, and down the hierarchy of nested measures in the calculation you could have a similar situation like the one described in the video, but in a more complex scenario where the presence of the issue is less obvious and much harder to investigate.

[@Milhouse77BS]

Always interested when I hear “never”.

[@mathew9665]

Interesting - how about a filter around a number of summarised columns like: VAR _FilteredWithoutFees = FILTER ( SUMMARIZECOLUMNS ( 'Sales'[AdminFee], 'Sales'[IsVoided], TransTypes[TransType] ), 'Sales'[AdminFee]

[@SQLBI]

The filter over columns in AND condition should be always split by column. Try to apply this filter to a CALCULATE with a non-additive measure (e.g. DISTINCTCOUNT, apply YTD, or use a bidirectional relationship) and you'll see the impact.

[@mathew9665]

@@SQLBI Thank you kindly - I don't understand how to use filter columns then build it into an in memory table. - what would be the best method to use in this type of requirement

[@SQLBI]

We suggest that you review the foundational concepts - use this free course to start: www.sqlbi.com/p/introducing-dax-video-course/

[@mathew9665]

@@SQLBI Thank you after posting - I did a lot of testing of the basic calculate filter - it works and a lot quicker

[@frasermartens3976]

Honestly, I sometimes think Microsoft should just completely remove the ability to filter a table. What's even the point of having this unnecessary and clumsy function in the language at all? Is there some arcane use case that it needs to be kept to address?