DAX LeftouterJoin No Relationship

[hnguyen76]

Hi All.

I'm attempting to create an attribute table from two fact tables in DAX. I'm fully aware that this is far easier and better to do in PowerQuery except that I'm using a database that has slow performance factor and the query takes forever to evaluate (even though I'm only pulling in a small amount of data). As an alternative I'd like to built this attribute table within DAX.

I want to pull just the distinct attributes from both fact tables so I can explode that within a table visual. These two facts are:

FACT_1

Ticket_ID	Country	Amount	SoldTo	SalesType	YearMonth	Date	ID.Key
1	US	100	Party1	Sales Order	202001	1-Jan-20	1^202001
2	US	102	Party1	Sales Order	202001	1-Jan-20	2^202001
3	CAD	104	Party2	Sales Order	202001	1-Jan-20	3^202001
4	CAD	500	Party3	Sales Order	202001	1-Jan-20	4^202001
5	US	5410	Party1	Sales Order	202001	1-Jan-20	5^202001
6	CAD	20	Party4	Sales Order	202001	1-Jan-20	6^202001
7	CAD	80	Party3	Sales Order	202001	1-Jan-20	7^202001
2	US	20	Party1	Sales Order	202001	1-Jan-20	2^202001
5	US	5400	Party1	Credit	202001	1-Jan-20	5^202001
5	US	400	Party1	Sales Order	202001	1-Jan-20	5^202001

FACT_2

Ticket_ID	Amount	Approved	Customer	YearMonth	Date	ID.Key
1	100	1	Customer 1 Name	202001	1-Jan-20	1^202001
2	102	0	Customer 1 Name	202001	1-Jan-20	2^202001
3	104	1	Customer 2 Name	202001	1-Jan-20	3^202001
4	500	0	Customer 3 Name	202001	1-Jan-20	4^202001
5	5400	1	Customer 1 Name	202001	1-Jan-20	5^202001
5	10	1	Customer 1 Name	202001	1-Jan-20	5^202001

Pretty common scenario.  If this is done in PowerQuery, I'd LEFTOUTERJOIN the FACT_1 ID.KEY to FACT_2 ID.KEY and return the distinct table values. This image result below is what I want:

As such, when I add this with my bridge table I'm retrieving the values I want:

So my attempt at DAX does not yield the expected result:

Any ideas on how to proceed?

[v-juanli-msft]

Hi @hnguyen76 

two ways to get expected result as you provided:

1. create relationships

2. create inactive relationships(inactive relationships won't filter tables)

Create calcualted columns

amount2 = CALCULATE(SUM(Table2[Amount]),USERELATIONSHIP(Table1[ID.Key],Table2[ID.Key]))

customer = LOOKUPVALUE(Table2[Customer],Table2[ID.Key],Table1[ID.Key])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hnguyen76 · Accepted solution]

@v-juanli-msft ,
Thanks for the reply. Unfortunately I was looking for a route that did not consider using any relationships (active or inactive) while actively avoiding M-2-M. Considering performance vs data model size I opted with my original solution to use PowerQuery 

[amitchandak]

You can use a cross join in Dax but it will make things slow. You can use it inside the filter to have your condition.

Also, use selectcolumns to rename and select columns as this will not Allow same name column.

Use summarize of top of it.

[hnguyen76]

@amitchandak ,

CROSSJOIN is definitely out of the question. I tried it with my dataset yesterday and it returned me 2 billion rows and took about 20 minutes to evaluate. 

[amitchandak]

I think the option you planning to use of power query is better. What you actally want to achieve using merge