Forum Discussion
Measure breaks inner join
I have three tables. There are two different types of sales tables linked to a Taxonomy table via the same fields (see screenshot). From these tables I am pulling Merch Taxonony as the row in a table, then assortment sales, and internet sales as values, then filtering by specific items. This works fine with just the rows for the relevant Merch Taxonomies, but when I create a measure for TY vs LY Internet Sales and drag that over, then I get every Merch Taxonomy in the left nav, even those with no Adobe or Internet sales. To fix this, I am currently adding a filter to remove all items where "Internet Sales is blank". However, this runs into issues where Assortment Sales has data when Internet Sales does not.
Here is my formula for the various to LY:
Internet Sales vs LY = Sum(Internet_Sales[Internet Sales $])/Sum(Internet_Sales[LY Weekly Internet Sales $])-1
There are no "inner joins" in the same sense there are inner joins with SQL. DAX joins are different. When the arrow points both ways, it is bi-directional cross filtering. The arrow indicates how filters flow, it doesn't limit data. You'd need to use Inner Join in Power Query to actually limit data.
You should probably read this article by SQLBI on joins in DAX, including a description of how the NATURALINNERJOIN() function works.
You may also want to see if CROSSFILTER() will help in your measure to activate cross filtering in a specific measure without forcing a bidirectional relationship, which can cause problems down the road.
2 Replies
There are no "inner joins" in the same sense there are inner joins with SQL. DAX joins are different. When the arrow points both ways, it is bi-directional cross filtering. The arrow indicates how filters flow, it doesn't limit data. You'd need to use Inner Join in Power Query to actually limit data.
You should probably read this article by SQLBI on joins in DAX, including a description of how the NATURALINNERJOIN() function works.
You may also want to see if CROSSFILTER() will help in your measure to activate cross filtering in a specific measure without forcing a bidirectional relationship, which can cause problems down the road.