cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Norbertus Member
Member

DAX filter data - SQL - NOT EXISTS

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. 

 

 

1 REPLY 1
Moderator Eric_Zhang
Moderator

Re: DAX filter data - SQL - NOT EXISTS

@Norbertus

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 391 members 4,265 guests
Please welcome our newest community members: