cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAX_Learner Regular Visitor
Regular Visitor

DAX query for full outer join

Please help me with the  DAX code  for below sql query.

 

Select pol.[Item No_],pol.Status,
sum(rl.[Quantity (Base)]) as PurchReq ,
sum(pol.[Remaining Qty_ (Base)]) as PlannedOrder
from [dbo].[Prod_ Order Line] pol
full outer join [Requisition Line] rl on rl.No_=pol.[Item No_]
group by pol.[Item No_],pol.Status

3 REPLIES 3
Community Support Team
Community Support Team

Re: DAX query for full outer join

Hi @DAX_Learner,

 

Please new a calculated table with below formula:

Table =
SUMMARIZE (
    NATURALLEFTOUTERJOIN ( 'Prod_ Order Line', 'Requisition Line' ),
    'Prod_ Order Line'[Item NO],
    'Prod_ Order Line'[Status],
    "PurchReq", SUM ( 'Requisition Line'[Quantity (Base)] ),
    "PlannedOrder", SUM ( 'Prod_ Order Line'[Remaining Qty_ (Base)] )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DAX_Learner Regular Visitor
Regular Visitor

Re: DAX query for full outer join

Thank you for your reply.

I did try with your given query but it is not giving required output.In power BI tables are connected with DATE table using Many to one relationship.

 

DAX output.PNGDAX output

 

sql_output.PNGRequired sql output

DAX_Learner Regular Visitor
Regular Visitor

Re: DAX query for full outer join

Worked with NATURALLEFTOUTERJOIN .Thank you @v-yulgu-msft.

 

But in the output its displaying only one column.
DAXOutput.PNG