Just wanted to confirm my understanding of using INTERSECT.
Have been going over the more advanced videos you post around INTERSECT (Video below).
I think it makes sense to me.
Basically INTERSECT function. When I break this down, visually it sounds like it is doing an INNER JOIN of Table 1 and Table 2? Only where Table 1 and Table 2 has common values it counts the id, or CALCULATE the sales. whatever the scenario is. (customer id)
Is this correct?
The videos are great! But on some of the more advanced DAX you record it would be really useful if in the future you would consider maybe visually breaking down what the DAX analytical engine is doing. Just simple 20 customer ID records (nothing over kill). I just feel it would make the learnnig curve more intuitive as opposed to intimidating
In your example, you are correct that INTERSECT and Inner Join will give you the same results. However, the two functions will return very different results if you have duplicate records in a table. Using your example, I created Table 3, which duplicates records for customers 11 and 12.
The INTERSECT function still returns the following:
But now the Inner Join returns:
Hope this is helpful.
Brian
P.S. Here’s a fantastic cheat sheet from Curbal that graphically depicts all the different types of joins and their equivalent DAX functions:
seriously the cheat sheet on Power BI and joins is seriously so awsome !!
Its just really helpful I guess when explaining these complex formulas (Dax) and having something visual to breaking down what EXCEPT, INTERSECT and all others functions are actually doing as opposed to just seeing a dax function in formula bar.
Regarding your example of having duplicating rows of customer ID (11 and 12)
I dont think it would make any difference, as we normally use VALUES before inserting in table function anyways.
all it would show if we used say example COUNTROWS
Customer ID 6 = 1
Customer ID 7 = 1
Customer ID 8 = 1
Customer ID 11 = 2
Customer ID 12 = 2
Its just really helpful I guess when explaining these complex formulas (Dax) and having something visual to breaking down what EXCEPT, INTERSECT and all others functions are actually doing as opposed to just seeing a dax function in formula bar.
That’s exactly why I love the “New Table” capability in the Modeling tab - provides a visual check that your DAX table functions are doing what you think they’re doing.
You could eliminate the duplicates in Table 3 by using VALUES, but those duplicates may actually be legitimate entries (e.g., same sales to same customers, but on different days). The key point is just the awareness that in certain circumstances, the two functions do not return the same results.
I am in the same boat for sure. Sometimes I have have a hard time visualizing the virtual tables inside my head. Especially when you add in context transition.
What I have found tremendously helpful is using Dax Studio. Have to write a query (vs. a measure or calculated table) but most of the time it’s simply just adding EVALUATE and then either producing a table for me to look at or if it is a measure I am interested in, using ROW.
Also I have found it faster than creating a table in PBI.
Anyhow, kust something to think about as there are a few ways to learn all of this, but just wanted to share what I have found most helpful for myself.
Thanks for the valuable input on this.
Dont get me wrong virtual tables (the more simple ones) i.e. Averages Per Day are fairly straight forward and intuitive to understand what the DAX engine is doing.
Its when we start using INTERSECT, CALCULATETABLE, EXCEPT etc etc.
I have looked into purchasing something like an Enterprise DNA but for DAX Studio to show more in depth (visually) the table calcs breakdown, full in depth A-Z online video training but I cannot find anything out there if im honest as good as Enterprise DNA and Sam teaching approach if im honest.
