How to show data from three related tables?

[LangeJan]

I am trying to create a relationship between three tables from AX2009.

1. Salestable,
2. Salesline
3. Custinvoicejour

Related column between all tables is SalesID.

SalesID will always exist in Salestable. SalesID not always exists in Salesline or Custinvoicejour.

I can display data in my visual if I connect only two of the tables. But as soon as I connect all three tables I get error in my visual visual.

Visual with only two tables related.

Visual with all three tables related

I expected my visual to show three columns of empty data in the fields where there is no data. Like example below. Is tha possible in PBI?

salestable.salesid	salesline.salesid	custinvoicejour.salesid
123456	123456	123456
123457	123457	Null
123458	Null	Null

Thank you

[LangeJan · Accepted solution]

I have solved my challenge by dropping the relationship between salestable and custinvoicejour. I have chosen that salestable and custinvoicjour exist as two separate fact tables that refer to the same dimension tables. It works.

[LangeJan]

@v-zhangti Thanks for your reply.

The result you show is exactly what I'm looking for, but I can't get that result out in my visual.

The relation between Salestable and Custinvoicejour/Salesline is one-to-many. Not one to one as in your example.

I have changed the cross filter direction as in your example, with no luck.

[v-zhangti]

Hi, @LangeJan 

Set the relationship between the three tables as shown.

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.