DAX filter data - SQL - NOT EXISTS

[Anonymous]

Hi,

Who can help me?

For a BI solution i have a dataset with many tabels (Orders, Orderdetail, etc etc)

In one visual I have to show all the records that is the result of the next query

SELECT DISTINCT o.oKey, o.OrderNumber, o.OrderType, o.AckDate, o.Date, ISNULL(os.TargetShipDate, '') AS TargetShipDate, o.ReqDate
		FROM FVMASTER.dbo.Orders o
			INNER JOIN FVMASTER.dbo.OrderDetail od ON o.okey=od.okey
			INNER JOIN FVMASTER.dbo.OrderDetailParts odp ON od.odKey=odp.odKey
			INNER JOIN FVMASTER.dbo.OrderDetailPurchaseReqs odpr ON odpr.odKey=odp.odKey AND odpr.MasterKey = odp.MasterKey AND odpr.ParentKey = odp.ParentKey
			LEFT JOIN (
					--Retrieve the earliest target ship date for any shipments (includes transfer shipments)
					SELECT oKey, MIN(TargetShipDate) AS TargetShipDate
					FROM FVMASTER.dbo.OrderShipments
					GROUP BY oKey
					) os ON o.oKey = os.oKey
			WHERE NOT EXISTS(
					SELECT * 
					FROM FVMASTER.dbo.OrderDetail od2 
					WHERE o.oKey=od2.oKey 
					AND od2.PurchasingReqsEvaluated=0 
					AND od2.ItemType<>5 AND od2.SubLineItem >= 0
					) 
					AND o.AckDate<>'' 
					AND o.OrderType IN (1,4,5) 
					AND o.ClosedDate='' 
					AND o.Cancelled=0 
					AND od.PurchasingReqsEvaluated=1 
					AND odpr.OrderDate=''
		
		GROUP BY o.oKey, o.OrderNumber, o.OrderType, o.AckDate, o.Date, os.TargetShipDate, o.ReqDate
		

I realy don't know how to realise this. 

At this moment I have a seperate SQL view that i load in PowerBi. But this is not the way to go, because this subdataset is not a part of mij datamodel. 

[Eric_Zhang]

@Anonymous

May I know where do you get stuck? You could try to import the tables individually into Power BI desktop tables and create proper relationship among them.

For further suggestion, is it possible to upload a pbix file containing your tables and expected output? You could upload the pbix file to a web storage like Onedrive and share a the link in a private message. Do mask sensitve data before uploading.