Cross Filter must be both to avoid cartesian/cross join results?

[sasdfasdfsad]

I have (what I thought) was a simple example.  Is there anyway to avoid getting cartesian/cross join results without setting Cross Filter to both if I want to display columns from different tables on the TABLE visual?

Customers table, and then a related Address and related Phone Numbers table:

The issue, of course, is that when I use Customers in a data grid and try to drag on rows from Phone Numbers and Addresses in the same grid it cross joins out (assuming I understand that correctly)

The only way around this that I could find was to set the Cross Filter direction to both:

Then I had to set it to show items with No Data otherwise it would only show rows where there were matches in all 3 tables (Customers will always have a match but there may not be Phone Numbers or Addresses for each customer)

Unchecked – “Show Items With No Data”

Checked – “Show Items With No Data”

That of course brings up the entire other issue with the TABLE visual where the order of columns impacts if you do or don't get values populated in rows "to the right", but let's just ignore that for now.

Data below:

Customers
CustomerID	Name
1	Jane Doe
2	John Doe
3	Sally Smith
4	Jessica Rabbit
5	Arthur Dent
6	Robert Johnson
Phone Numbers
PKID	CustomerID	Number
333	1	555-555-5555
984	1	555-555-6666
185	4	555-555-7777
91	5	555-555-8888
Addresses
PKID	CustomerID	Address Full
55	2	123 Fun Street
89	3	839 47th Ave
125	5	2293 Horizon Ave
25	2	991 Eastern Glacier Drive
58	5	4 New Haven Ave
137	3	23 Main Street
489	6	22 Main Street

[Ashish_Mathur]

Hi,

Based on the 3 tables that you have shared, show the expected result.

[sasdfasdfsad]

I was hoping to get a layout like this:

 Where rows would not be surpressed.  I'd rather have rows repeated once for each.

[Ashish_Mathur]

Hi,

Perhaps i cannot understand your requirement.  Someone else will help you.

[sasdfasdfsad]

Which occurs to me that's the same behavior from a TSQL INNER JOIN... EXCEPT for an INNER JOIN to work I would need a row in each table to join on.

It looks like I'll have to insert a place holder row into each table then???

Is there a way to simulate that functionality without having to populate the tables that way?

A measure using CROSS JOIN OR CROSSFILTER or something?

Data tables with added rows 

Phone Numbers
PKID	CustomerID	Number
333	1	555-555-5555
984	1	555-555-6666
185	4	555-555-7777
91	5	555-555-8888
95	6
96	2
97	3
Addresses
PKID	CustomerID	Address Full
55	2	123 Fun Street
89	3	839 47th Ave
125	5	2293 Horizon Ave
25	2	991 Eastern Glacier Drive
58	5	4 New Haven Ave
137	3	23 Main Street
489	6	22 Main Street
98	1
773	4

[Anonymous · Accepted solution]

Hi @sasdfasdfsad 

You can conider to realize it in power query by using merge queries in power query, 

Then expanded the related columns

Output

You can refer to the following link about the merge queries

Merge queries overview - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

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