Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |