"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!
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
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.
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.
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.
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
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 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 :)
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.
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).
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.
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.
Interesting - how about a filter around a number of summarised columns like: VAR _FilteredWithoutFees = FILTER ( SUMMARIZECOLUMNS ( 'Sales'[AdminFee], 'Sales'[IsVoided], TransTypes[TransType] ), 'Sales'[AdminFee]
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.
@@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
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
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?
"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!
This 100%
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
I've been waiting for an in depth explanation around this golden rule for a while; thank you!!
Do you have a video/ playlist with all the golden rules n best practices. Love your work!! ❤
I watched this early in the morning. Now I don't need my morning coffee. This blew my mind!
Another Fantastic one.Alberto & Marco always opens new horizons in DAX to think over.
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.
if there is a nobel for DAX, this guy deserve it !
Counting with Alberto! Love it!!
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.
Thanks for this insight on expanded tables.
Thanks Sir ji after a long time create a nice video
Thank you. A great article/video on this important golden rule!
Great explantion, Thanks!
SQLBI: Masters at work.
Thank you for sharing this. really insightful.
Thank you for sharing this ❤
Nice video as usual 😎👏
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.
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
Yes, see this article for the fundamentals: www.sqlbi.com/articles/filter-arguments-in-calculate/
Thanks for sharing! I think it would be nice to have a video about when the use of filters in tables is unavoidable.
Never?
Please upload more dax videos related summarize and ADDCOLUMNS together usage
Nice explanation
Thank you!
Molto interessante!
C'è un elenco di queste "regole fondamentali" da qualche parte?
Thanks, it's really useful
Excellent 👌
Why have you used KEEPFILTERS in the correct measure? I would just write as you did before without that function. It would be wrong to do so?
It's to keep the same semantics, even though it's not needed for the report used in the demo.
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 !! ❤
You have a full pattern here: www.daxpatterns.com/abc-classification/
Very insightful
@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 :)
It is converted to a column filter, not to a table filter: www.sqlbi.com/articles/filter-arguments-in-calculate/
@@SQLBI Ah,That means FILTER(ALL(Sales[Quantity],Sales[Net Price]), Sales[Quantity] * Sales[Net Price] >= 500) etc..?
@@Nalaka-Wanniarachchimy question exactly! Need to try it on a model to make sure it works like this or not
This is brilliant
thank you
Thank you for Sir
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.
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).
The DAX magister
The screenshot perfectly describes me when DAXing...
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.
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.
Interesting - how about a filter around a number of summarised columns like:
VAR _FilteredWithoutFees =
FILTER (
SUMMARIZECOLUMNS (
'Sales'[AdminFee],
'Sales'[IsVoided],
TransTypes[TransType]
),
'Sales'[AdminFee]
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.
@@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
We suggest that you review the foundational concepts - use this free course to start: www.sqlbi.com/p/introducing-dax-video-course/
@@SQLBI Thank you after posting - I did a lot of testing of the basic calculate filter - it works and a lot quicker
Billion !!!... not Million !!!.... But other than that thanks. Very helpful!
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
Always interested when I hear “never”.
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?