Reply
Highlighted
Member
Posts: 213
Registered: ‎04-14-2017

Struggling with NATURALLEFTJOIN

I have created 2 tables which both contain the field [Customer No]

 

Table = summarizecolumns( Customer[Customer No],Customer[Salutation],Customer[First Name],Customer[Surname],Customer[Customer Active],Customer[Customer Group],Customer[Last Centre],Customer[E-Mail],Customer[Mobile Phone No],Customer[Address],Customer[Address 2],Customer[City],Customer[Post Code],Customer[Last Date Visited],Customer[Plat MOT Avail],Customer[Plat XS Avail], Customer[Plat Full Avail], Customer[Plat Int Avail],Customer[MOT Reminder Platinum],Customer[XS Reminder Platinum],FILTER(Customer,Customer[CRM Output]="yes")     )

amd

 

Table 2 = summarizecolumns( vehicles[Customer No],  Vehicles[VRM],FILTER(Customer,Customer[CRM Output]="yes") ,FILTER(Vehicles,Vehicles[Vehicle Visit Rank]=1 && Vehicles[Vehicle Output]="yes")    )

i have then created a relationship between Table 2 and Table 1 using [Customer no].  noty all customers in Table have a VRM in Table 2, but i want to output all rows from Table with the mnatching data form Table 2 if available. 

 

I therefore created a 3rd table expecting to get that, but get an error "The column with the name of "custoemr no" already exisits in Table 3"??  I wasn't expecting that as Cusotmer no is the ralated field.  I was following this article https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

 

Table 3 = NATURALLEFTOUTERJOIN('Table','Table 2')

Can anyone tell me what i'm doing wrong please?

Mike

New Contributor
Posts: 634
Registered: ‎09-16-2018

Re: Struggling with NATURALLEFTJOIN

Hello @masplin

 

You need to either remove the relationship or rename Customer No to something else in one of the two tables if you need to keep the relationship.