Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Matt_H_Clam
Frequent Visitor

Converting excel connection to Dax - ie SQL to Power Query and DAX

Ive inherited a spreadsheet that connects to a local SQL database with a command string and the following command text;

 

SELECT shipmentitems.partid, shipmentitems.ordertype, invoiceitems.quantity, invoiceitems.createddate, shipments.traderid, invoiceitems.homeunitprice, partrevisions.bommethod, partrevisions.routingmethod, allpartmaster.sm, orderitems.vatcode, allpartmaster.fsunit, invoiceitems.currencyunitprice, allpartmaster.casting, invoices.taxdate, invoices.status, allpartmaster.fsmat, allpartmaster.fssubcon
FROM efacdb.dbo.allpartmaster allpartmaster, efacdb.dbo.invoiceitems invoiceitems, efacdb.dbo.invoices invoices, efacdb.dbo.orderitems orderitems, efacdb.dbo.partrevisions partrevisions, efacdb.dbo.shipmentitems shipmentitems, efacdb.dbo.shipments shipments
WHERE orderitems.orderid = shipmentitems.orderid AND shipmentitems.shipmentid = shipments.id AND orderitems.itemnumber = shipmentitems.orderitemnumber AND invoiceitems.shipmentid = shipmentitems.shipmentid AND shipmentitems.itemnumber = invoiceitems.shipmentitemnumber AND partrevisions.partid = invoiceitems.partid AND partrevisions.partid = orderitems.partid AND partrevisions.partid = shipmentitems.partid AND partrevisions.partrevisionid = orderitems.partrevisionid AND partrevisions.partrevisionid = shipmentitems.partrevisionid AND invoiceitems.partid = allpartmaster.partnum AND invoiceitems.invoiceid = invoices.id AND ((shipmentitems.ordertype='SO'))
ORDER BY shipmentitems.partid, shipments.traderid, invoiceitems.createddate

 

 

I wish to recreate as a data model in PowerBI desktop then publish to the service using our gateway.

 

So far in desktop I have created a connection to the database in Power Query and imported the relevant tables no problem (with filters to limit data to the last 12 months)

 

I have also created 1 to many relationships where the above command says a field in one table equals one in another or created a master table for fields with a many to many.  This covers the FROM and WHERE

 

Being a novice here with the Select part of the command im looking to create a new table in the model using DAX and my  thought process was to use;

 

New Table = SUMMARIZECOLUMNS(

                                                             shipmentitems[part.id], shipmentitems[ordertype], invoiceitems[quantity], shipments[traderid] etc etc 

)

 

 

the new table is hanging when i press enter and i just get the 'im working on it' message.

 

Is there a better way? or have i fundamentally got it right/wrong?

 

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@Matt_H_Clam , if data is really big, then having a dax table is not a good idea.  It can also issues because of system performance.  Check RAM and Hard disk availability

 

Try to bring this from DB, in not try in power query

Thanks. I am using 20%CPU and 4GB of memory on my laptop as its trying to run.. I have 32GB RAM and a Core i7 2.8ghz Processsor so its pretty meaty

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.