Fusion is an optimization technique used by VertiPaq. Auto-exists is a behavior that has been created for performance reasons, but it could have the side effects described in the video.
I would SO VERY MUCH appreciate a video on how to WORK AROUND this issue!!!! I would buy a book, pay money, etc! I am haunted by this "feature" daily. Its such a simple need to have multiple filters and a measure that uses some ALL() in the calculation. I never know if my number is correct.
I think this issue is the root of a problem I've been looking at for a few months. Really helpful. Would love to see a video of the top ten "Dax Shenanigans". Would be great to reinforce best practice. My issue was that I hadn't heard of this issue, and was working in a test environment with a simple fact table and a calendar table. Because the fact table was so small, 15 rows I didn't think it was important to model the data (i'd read that this was more for performance issues in very large datasets), but it clearly is!
Amazingly presented!! Thank you, Alberto! I think the condition you have highlighted @ 14:00 on the Subject column is wrong. The Subject which should be highlighted is only those which also have the Marks: Then answer then would be correct i.e., 145. So I think it applies Filter simultaneously and not first on Subject and then on Marks.
Watching this for an umpteenth time... I've finally understood how to "prevent" this auto-exist BUG (to me it's a bug, will never be a feature) on dimension tables (fact tables should always be hidden and their columns should never be sliced by, anyway). The only way to do it is to create a dimension table that would be a full cross-join between all its attributes' values. Meaning, if one attribute has values in {a, b, c} and another in {1, 2, 3}, one has to make sure that all the combinations exist in the dimension, that is (a, 1), (a, 2), (a, 3),...(c, 3), even though some of them might have no corresponding rows in the fact table. But it's the only way TODAY to make sure that the model will always calculate correct numbers from correct formulas. Kind of... Eureka, I guess. Of course, if a dimension is built with columns that constitute a natural hierarchy, this is not needed. But only if the hierarchy is natural, which - to be frank - mostly happens only in date tables, much less on other dimensions.
Hi, I totally agree with you; it's a bug... It cannot be a feature. Here we are accepting that it is normal that: if (a==1) {...code...} and if ( (a==1) && (1==1) ) {...code...} have a different behavior!! Microsoft please fix this bug!
Concerning this annoying bug in DAX Studio where the box sticks no matter what ( th-cam.com/video/aRntX-HiiN8/w-d-xo.html ) , the simplest solution I found is to press escape on the keyboard. Hopefully it helps other people.
Yes, Alberto tried that in the video, but sometimes it is caused by something else. However, the good news is that Darren probably fixed a related bug, so the next release of DAX Studio could have a fix for that!
Another excellent video; curious if the auto-exist functionality is implemented as a way of tuning performance? Put another way, is there a positive / helpful use case of auto-exists?
A debugging tool that has helped me narrow down the problem, in similar situations, is the following measure: = CALCULATE ( CONCATENATEX ( FILTERS ( Table1[column1] ), Table1[column1], "; " ), ALLSELECTED ( Table1[column1] ) ) This measure will show which values are in the effective filter on a specific column after the autoexist has done it's ( ill-fated ) thing. :) It's a bit unfortunate that Excel and powerBI differ in the autoexist aspect. The Excel MDX SUBSELECT etc. does not suffer from the scenario described in the video and you often use Excel as a mean to test and show edge cases. Excel is great at showing many, many values and drill down levels on a 32" Monitor. :)
This is absolutely crazy. How it is possible that a tool can be so complicated? Who will take into account such things in every calculation? It seems that many calculation are wrong because of feature which is known to a small number of people. Absolutely counterintuitive and complex. This is another reason I really want to quit. There are more exceptions then rules and there is no hint for that. There are more scenarios where a simple calculation does not work with all that knowledge. How it is even possible that You set some exact rules for filter (marks between 23-49) and the engine changes it by its own during code execution (for every 45 and 23 as a table). And even why the rule which firstly uses "more then 23 and less then 49" like ="filter [x] > 23 AND filter[x] < 49" changes to table, why it is not executed once more to iterate over values between 23-49. How can You know when the right filter context it stored and used?
This is one of the several reasons why it is better to use a star schema. However, the auto-exists could still generate unexpected results in certain side cases with just dimensions, but in general you do not see surprises in star schemas. We know that this part is not that good, but if the product works this way, we can only describe it!
I am absolutely gratefull for work You do as SQLBI. Thank You. Your Team is amazing and help me a lot with the book and website. After 2 years with DAX I would say it straighforward - this product is very badly designed. Not for regular user. Relatively simple math and ideas calculations are like climbing on MT Everest. Work should be based on ideas but 90% of time is wasted on figuring out why something does not work and if it works then how:). I learnt it a lot but still simple ideas are hard to implement.
Well it seems that the Junk Dims Table (which keeps all existing combinations of dims in Power Query that I created can introduce this kind of problem which is absolutely counter intuitive and now the table is useless as I have checked with some tests in it produces errors with auto exist. The user like me try to follow best practices but it creates problems which are very possible to show up. Does it mean that for every DIM table we need to create additional one column tables with specific dim? I guess it is a must but it is ridiculous. What about date table?
@@SQLBI I made a Junk Dim (currency, status, state, store name, payment method) from 5 columns from a Order Table (in the model it is then denormalized with allocation in Order Line Table) , than this Junk Dim was merged it with carrier name (it is possible then 1 order can have many carriers) from other table by Order number. So finally Junk contains 5 columns, I delete duplicates of these combinations. At the end I made a KEY for each combination so the Junk Dim could filter 3 fact tables. This Junk Dim is not Cartesian product of all possible combinations. It contains only existing combinations. My Junk Dim had unique 75 combinations, Cartesian product made from these columns would give me...13 824 rows !!! After reading an article and video I did a test and auto exist results in wrong calculations with some filters from Junk Dim and with ie. "ALL(carrier name). Huge nominal differences in values at the end. I am in absolute shock that this kind of operation is executed without control and it exist in such a tool like BI. In the same model a DIM product contains 10 attributes, it seems that I need to snowflake each of them with seems really ridiculous. So at the end it makes some kind of fact table from products DIM. How can anyone called this a feature, it is a huge bug in my opinion which leads to wrong calculations
It's an optimization that has side effects. Hopefully there will be the option to disable it. Don't shoot on the messenger, we didn't design the product! :)
Such clear explanation as always!
I don't even think Microsoft knows this much about DAX 😂
I'm confused. My understanding is that the combining of filters is due to Fusion. Why are you primarily talking about auto-exist?
Fusion is an optimization technique used by VertiPaq.
Auto-exists is a behavior that has been created for performance reasons, but it could have the side effects described in the video.
Why is auto-exist called auto-exist?
Because calling it BUG should have forced somebody to work to fix it. So doing it is a "feature".
I would SO VERY MUCH appreciate a video on how to WORK AROUND this issue!!!! I would buy a book, pay money, etc! I am haunted by this "feature" daily. Its such a simple need to have multiple filters and a measure that uses some ALL() in the calculation. I never know if my number is correct.
I think this issue is the root of a problem I've been looking at for a few months. Really helpful. Would love to see a video of the top ten "Dax Shenanigans". Would be great to reinforce best practice. My issue was that I hadn't heard of this issue, and was working in a test environment with a simple fact table and a calendar table. Because the fact table was so small, 15 rows I didn't think it was important to model the data (i'd read that this was more for performance issues in very large datasets), but it clearly is!
Amazingly presented!! Thank you, Alberto!
I think the condition you have highlighted @ 14:00 on the Subject column is wrong. The Subject which should be highlighted is only those which also have the Marks: Then answer then would be correct i.e., 145. So I think it applies Filter simultaneously and not first on Subject and then on Marks.
Watching this for an umpteenth time... I've finally understood how to "prevent" this auto-exist BUG (to me it's a bug, will never be a feature) on dimension tables (fact tables should always be hidden and their columns should never be sliced by, anyway). The only way to do it is to create a dimension table that would be a full cross-join between all its attributes' values. Meaning, if one attribute has values in {a, b, c} and another in {1, 2, 3}, one has to make sure that all the combinations exist in the dimension, that is (a, 1), (a, 2), (a, 3),...(c, 3), even though some of them might have no corresponding rows in the fact table. But it's the only way TODAY to make sure that the model will always calculate correct numbers from correct formulas. Kind of... Eureka, I guess. Of course, if a dimension is built with columns that constitute a natural hierarchy, this is not needed. But only if the hierarchy is natural, which - to be frank - mostly happens only in date tables, much less on other dimensions.
Hi, I totally agree with you; it's a bug... It cannot be a feature.
Here we are accepting that it is normal that:
if (a==1) {...code...}
and
if ( (a==1) && (1==1) ) {...code...}
have a different behavior!!
Microsoft please fix this bug!
Thx For example. I'm learning more and more from dax. I like it. Despite it is more for the hobby
Alberto when i see your videos indeed there are levels of understanding in DAX
and to become just ninja is very hard 👍
So, that s why is better a star schema. Mmm. What an experience you have.
This auto-exists scenario has always perplexed me but now I finally get it! Thanks Alberto. A really simple and concise explanation!
Excellent video, with nice diagnostic methodology you can also apply to other BI issues
I always enjoy your videos and very detailed explanation.Many thanks.
Those UNPLUGGED videos open my mind to how to use the tools to investigate the Dax errors. I LIKE THEM!! BIG THANKS TO YOU
Concerning this annoying bug in DAX Studio where the box sticks no matter what ( th-cam.com/video/aRntX-HiiN8/w-d-xo.html ) , the simplest solution I found is to press escape on the keyboard. Hopefully it helps other people.
Yes, Alberto tried that in the video, but sometimes it is caused by something else. However, the good news is that Darren probably fixed a related bug, so the next release of DAX Studio could have a fix for that!
@@SQLBI thank you for the answer and the update about Darren fixing something similar :). Looking forward your next article/video.
Absolute masterclass!
Superb! As always expert explanation. Thanks Alberto.
Excellent info. Thanks for sharing. Definitely supports the best practice of star schema and of filtering on dimension tables.
Another mastreclass..... Grazie mille Alberto¡¡¡
Excellent, very interesting, thank you so much for making these videos that teach us so much!
Another good reason to hide the keys on the fact tables!
I had no idea of this ! thanks so much Alberto
Thanks for the knowledge 😊
Another excellent video; curious if the auto-exist functionality is implemented as a way of tuning performance? Put another way, is there a positive / helpful use case of auto-exists?
I realise the answer to my question is on the SQLBI article!
A debugging tool that has helped me narrow down the problem, in similar situations, is the following measure:
=
CALCULATE (
CONCATENATEX (
FILTERS ( Table1[column1] ),
Table1[column1],
"; "
),
ALLSELECTED ( Table1[column1] )
)
This measure will show which values are in the effective filter on a specific column after the autoexist has done it's ( ill-fated ) thing. :)
It's a bit unfortunate that Excel and powerBI differ in the autoexist aspect. The Excel MDX SUBSELECT etc. does not suffer from the scenario described in the video and you often use Excel as a mean to test and show edge cases. Excel is great at showing many, many values and drill down levels on a 32" Monitor. :)
there looks like a new visualization between arcgis map and smart narratives viz. anybody know what that is? paginated report builder ?
This is absolutely crazy. How it is possible that a tool can be so complicated? Who will take into account such things in every calculation? It seems that many calculation are wrong because of feature which is known to a small number of people. Absolutely counterintuitive and complex. This is another reason I really want to quit. There are more exceptions then rules and there is no hint for that. There are more scenarios where a simple calculation does not work with all that knowledge. How it is even possible that You set some exact rules for filter (marks between 23-49) and the engine changes it by its own during code execution (for every 45 and 23 as a table). And even why the rule which firstly uses "more then 23 and less then 49" like ="filter [x] > 23 AND filter[x] < 49" changes to table, why it is not executed once more to iterate over values between 23-49. How can You know when the right filter context it stored and used?
This is one of the several reasons why it is better to use a star schema. However, the auto-exists could still generate unexpected results in certain side cases with just dimensions, but in general you do not see surprises in star schemas.
We know that this part is not that good, but if the product works this way, we can only describe it!
I am absolutely gratefull for work You do as SQLBI. Thank You. Your Team is amazing and help me a lot with the book and website. After 2 years with DAX I would say it straighforward - this product is very badly designed. Not for regular user. Relatively simple math and ideas calculations are like climbing on MT Everest. Work should be based on ideas but 90% of time is wasted on figuring out why something does not work and if it works then how:). I learnt it a lot but still simple ideas are hard to implement.
Well it seems that the Junk Dims Table (which keeps all existing combinations of dims in Power Query that I created can introduce this kind of problem which is absolutely counter intuitive and now the table is useless as I have checked with some tests in it produces errors with auto exist. The user like me try to follow best practices but it creates problems which are very possible to show up. Does it mean that for every DIM table we need to create additional one column tables with specific dim? I guess it is a must but it is ridiculous. What about date table?
Not sure we follow - if a junk dimension contains all the possible combinations, it should not be subject to auto-exist limitations. Can you clarify?
@@SQLBI I made a Junk Dim (currency, status, state, store name, payment method) from 5 columns from a Order Table (in the model it is then denormalized with allocation in Order Line Table) , than this Junk Dim was merged it with carrier name (it is possible then 1 order can have many carriers) from other table by Order number. So finally Junk contains 5 columns, I delete duplicates of these combinations. At the end I made a KEY for each combination so the Junk Dim could filter 3 fact tables. This Junk Dim is not Cartesian product of all possible combinations. It contains only existing combinations. My Junk Dim had unique 75 combinations, Cartesian product made from these columns would give me...13 824 rows !!!
After reading an article and video I did a test and auto exist results in wrong calculations with some filters from Junk Dim and with ie. "ALL(carrier name). Huge nominal differences in values at the end. I am in absolute shock that this kind of operation is executed without control and it exist in such a tool like BI. In the same model a DIM product contains 10 attributes, it seems that I need to snowflake each of them with seems really ridiculous. So at the end it makes some kind of fact table from products DIM. How can anyone called this a feature, it is a huge bug in my opinion which leads to wrong calculations
It's an optimization that has side effects. Hopefully there will be the option to disable it. Don't shoot on the messenger, we didn't design the product! :)
@@SQLBI i am sry if it feels like i shoot the messenger. Absolutely not my intention. I am super grateful for you work.