topic Re: naturalinnerjoin date in DAX Commands and Tips

naturalinnerjoin date

Anonymous — Mon, 16 Mar 2020 10:41:35 GMT

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 ?

Re: naturalinnerjoin date

Anonymous — Mon, 16 Mar 2020 11:03:42 GMT

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

Re: naturalinnerjoin date

Anonymous — Mon, 16 Mar 2020 11:06:16 GMT

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

Re: naturalinnerjoin date

Greg_Deckler — Mon, 16 Mar 2020 12:35:00 GMT

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.

Re: naturalinnerjoin date

Anonymous — Wed, 18 Mar 2020 08:52:05 GMT

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