How joins works in Power BI

[FrankNY007]

Hello,

I met some problem to figure out how the join works in PowerBI. let us see I have three tables below:

Order Table: (Main table, contain all order information)

Order ID	Location	Sales
1990	NJ	$10
1991	NY	$12
1992	CT	$45
1993	DC	$23
1994	NC	$43

Phone Table:( Contains reconrd and time when we contact customer, one order may have mutiple records, since we contact customer at different time, and it only contain EDR optinon, which menas in column option, it all EDR)

Order ID	Option	Call Time
1990	EDR	6/27/2018 16:53
1990	EDR	6/27/2018 14:53
1991	EDR	6/27/2018 19:53
1992	EDR	6/27/2018 22:53

Scanning Table:(Contain scanning data, one order one record, Early Delivery Flag menas delivery early than expetected time)

Order ID	Early Delivey Flag
1990	Y
1991	Y
1993	Y

I import the data into power bi and buil the relationship:

Now if I only bring Order.Order ID and Scanning.Early Delivey Flag, I got this, so even order ID that didn't has record in scanning will show( for me, it is like a outer join)

Then if I bring Order.Order ID and Phone.Option in, I only got orders that has records in Phone table(it is becasue of it is 1:M relationshio??)(inner join??

My goal is to count how many order in order tbale, than has early flay="Y' but not has record in phone table, for those sample data only 1993 matches, so I should got 1.

However I canont do it, use cacalate(distinctconut(order.oder_id),filter(phone,phone.option<>"EDR"), filter(scaning.early_flag="Y"), it return null

if i change to cacalate(distinctconut(order.oder_id),filter(phone,phone.option="EDR"), filter(scaning.early_flag="Y"), it give me 2 (1990, 1991 clearly).

So what happens here? Intersting thing is: if I delete any duplicate reconrd in my phone call table, and make it 1:1 to my order table, then my fomula works

[Anonymous]

---

@FrankNY007 wrote:

Hello,

I met some problem to figure out how the join works in PowerBI. let us see I have three tables below:

Order Table: (Main table, contain all order information)

Order ID	Location	Sales
1990	NJ	$10
1991	NY	$12
1992	CT	$45
1993	DC	$23
1994	NC	$43

Phone Table:( Contains reconrd and time when we contact customer, one order may have mutiple records, since we contact customer at different time, and it only contain EDR optinon, which menas in column option, it all EDR)

Order ID	Option	Call Time
1990	EDR	6/27/2018 16:53
1990	EDR	6/27/2018 14:53
1991	EDR	6/27/2018 19:53
1992	EDR	6/27/2018 22:53

Scanning Table:(Contain scanning data, one order one record, Early Delivery Flag menas delivery early than expetected time)

Order ID	Early Delivey Flag
1990	Y
1991	Y
1993	Y

I import the data into power bi and buil the relationship:

Now if I only bring Order.Order ID and Scanning.Early Delivey Flag, I got this, so even order ID that didn't has record in scanning will show( for me, it is like a outer join)

Then if I bring Order.Order ID and Phone.Option in, I only got orders that has records in Phone table(it is becasue of it is 1:M relationshio??)(inner join??

My goal is to count how many order in order tbale, than has early flay="Y' but not has record in phone table, for those sample data only 1993 matches, so I should got 1.

However I canont do it, use cacalate(distinctconut(order.oder_id),filter(phone,phone.option<>"EDR"), filter(scaning.early_flag="Y"), it return null

if i change to cacalate(distinctconut(order.oder_id),filter(phone,phone.option="EDR"), filter(scaning.early_flag="Y"), it give me 2 (1990, 1991 clearly).

So what happens here? Intersting thing is: if I delete any duplicate reconrd in my phone call table, and make it 1:1 to my order table, then my fomula works

---

Power BI Joins come into picture when we try to Merge 2 queries:

https://www.powerbi-pro.com/en/power-bi-seven-types-of-table-joins/

[FrankNY007]

Any Idea?

[v-qiuyu-msft]

Hi @FrankNY007,

In your scenario, you can create a measure below: 

Measure = var t=FILTER('Order',ISBLANK(LOOKUPVALUE(PhoneCall[Order ID],'PhoneCall'[Order ID],'Order'[Order ID])))
return
COUNTX(FILTER(t,RELATED('Scanning'[Early Delivey Flag])="Y"),[Order ID])

Best Regards,
Qiuyun Yu