Full outer join two tables with 100's of millions of rows each

[Jack2of3]

Greetings. Absolutely new to Power BI, DAX, etc. Decades of experience in ETL, data modeling, OBIEE. 

What I need to do as a POC is pull all values from one table and populate a third value from a second table.

I will then do trend and comparative analysis on the output with filters etc. I've tried merging the tables but with the amount of data it is too slow. What I've read so far suggests using a DAX formula is the way to go, aside from using a dedicated ETL tool, to form the data. 

In the snapshots below TAP_DATA has values TOTALS for LINES_ROUTE when DATE_HOUR has a reading.

TAP_LKP has every LINE_ROUTE and every DATE_HOUR.

I need to select all rows from TAP_LKP and the TOTAL row from TAP_DATA and populate 'null' or 0 in the TOTAL row when TAP_DATA is missing a join on LINE_ROUTE and DATE_HOUR

TAP_DATA

TAP_LKP

[parry2k · Accepted solution]

@Jack2of3  it is correct that using DAX will be the way to go, to make it work first we need to make a key to set relationship between these two table and based on your input:

- the relationship between these two table is 1 to 1, correct?

- there might be missing records in TAP_DATA table, means not necessarily each line_route + date hour will have a record, correct?

- there will be always one record for each route and date_Hour in tap_data, correct?

add column in each table to set relationship:

UniqueKey = TAP_DATA[Line_Route] && TAP_DATA[Date_Hour]


UniqueKey = TAP_LKP[Line_Route] && TAP_LKP[Date_Hour]

in relatioship, set relationship betwee these two table on this newly added "uniquekey" column, add following column in TAP_LKP to get Total from TAP_DATA

Total = RELATED( TAP_DATA[Total] )

I made some assumption based on your question, we can always tweak above approach if something I missed.

[parry2k · Accepted solution]

@Jack2of3  it is correct that using DAX will be the way to go, to make it work first we need to make a key to set relationship between these two table and based on your input:

- the relationship between these two table is 1 to 1, correct?

- there might be missing records in TAP_DATA table, means not necessarily each line_route + date hour will have a record, correct?

- there will be always one record for each route and date_Hour in tap_data, correct?

add column in each table to set relationship:

UniqueKey = TAP_DATA[Line_Route] && TAP_DATA[Date_Hour]


UniqueKey = TAP_LKP[Line_Route] && TAP_LKP[Date_Hour]

in relatioship, set relationship betwee these two table on this newly added "uniquekey" column, add following column in TAP_LKP to get Total from TAP_DATA

Total = RELATED( TAP_DATA[Total] )

I made some assumption based on your question, we can always tweak above approach if something I missed.

[Jack2of3 · Accepted solution]

Thanks @parry2k  You have the description of the data exactly right. The solution worked however, with such a large amount of data and processing it on the Desk Top it is very slow.

I think I'll do it the right way and process the data via an ETL application and denormalize the input data so all Power BI needs to do is slice and dice the data.