naturalinnerjoin date

[Anonymous]

Hi folks!!

Hope you are doing well, and working from home 😉

I started to fight with NATURALINNERJOIN function...I hope not to lose 😉

I created 2 tables which are agregation of time from 2 different sources. 

I want to join them  on the date and the location

Table 1 structure

- Date

- Location 

- Time (summation)

Table 2: 

- Date

- Location

- Time worked

- Vacation

When I use the naturalinnerjoin(Table1, Table2) function, it tells me : Date is already part of the table...which is actually the purpose 😉

What do I miss ?

PY

[Greg_Deckler]

@Anonymous - You can use SELECTCOLUMNS when joining tables using NATURALINNERJOIN to rename any columns that are the same. In your case, if you want to join on two columns, you would likely want to concatenate those columns together and then join on that.

[Anonymous]

Dear All,

Thanks for your feedback!

Actually, I managed to use the relationships to overcome my goal. I reviewed the star schema I created...

Thanks a lot

PY

[Anonymous]

I'll tell you more. You should extract Date and Location into their own dimensions and then create a star schema with 2 fact tables. Please see this to know how to correctly model in Power BI:

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

Best
D

[Anonymous · Accepted solution]

Use COMBINEVALUES to create a calculated column in your tables and then create a relationship between them. Using JOINS like this in DAX is not the best solution or even Best Practice.

Relationships are the best solution and the fastest.

Best
D