Using InnerJoin


#1

Hi,
I am trying to join two fact tables through innerjoin. They both have a single column in common, but there is no other relationship among the two. “NaturalInnerJoin” of DAX doesnot seem to work without proper relationship built between the tables. Is there different way to achieve this?. Thanks.


#2

Might be missing something here however you inner join will allow you merge the contents of both tables using that common dimension (data column) found in both tables, the ultimate result being one bigger "flattened table housing the contents of all rows common to both tables (where you have selected the columns of the second table that you are bringing into the enlarged flattened table…ultimately resulting in a single bigger table). By doing that you are effectively negating the need to build a relationship between both tables in Dax as you have done exactly that in PQ by essentially flattening both tables…Have I misunderstood?


#3

As an alternative consider table de-aggregating the larger of the 2 fact tables by referencing a copy from a staging table in PQ and removing all columns apart from that column common to both, then remove all duplicates in that column and use that puppy as a lookup table for both fact tables. Then in the data model build a relationship between that Lookup and 2 fact tables remembering to hide the dimensions on both Fact tables for that relationship such that your users don’t accidently cuase themselves problems down the road


#5

I found this, “TreatAs” in Dax which could be used to join tables when there is no physical relationship exists. A sample of my case would look like

SUMMARIZECOLUMNS (
‘Location’[Country],
‘Location’[City],
“Sum Of Sales per location”, CALCULATE (
Sum(Sales[Total Sales]),
TREATAS ( VALUES ( ‘Sales’[Location ID] ), ‘Location’[Id] )
)
)
This is assigned to new table. Unsure if I am on the right path.


#6

I have seen Sam use both the TREATAS function and the disaggregation method @eamonn mentioned to achieve the desired relationship results. An actual defined relationship will allow more/easier filtering and aggregating, though.

If your example for TREATAS is representative of your data, it would seem that you have a Location table which contains a list of locations and all associated metadata (ID, City, Country, etc.). This table could be considered a dimension table, and therefore it may be okay to define a relationship on the data model tab in PowerBI between the Location[Id] and Sales[Location ID] columns.


#7

Thanks for the response @jpratt . But in the actual scenario, I do not have access to the Dimension table. I wonder what is the right way to handle such scenario…


#8

@sripalavi, so in that case I would recommend creating your own dimension/lookup table for all distinct locations described in one or both fact tables, following what @eamonn described and/or what I just watched Sam teach about in his Advanced Data Transformation and Modeling course (section “What to do if you don’t have a lookup table”). You can then link that lookup table to both fact tables and allow the filtering to “flow” as Sam teaches.