Pulling large data from SQL server and filters

Hi, I am trying to automate a process.  The initial data is coming from sql server.  This data is over 500k rows so the initial load is even slow. My business partner will evenually use each account for a balance sheet in a separate report (many accounts and reports). What is the best way to set this up and how should I then create a filter or report on the account while still managing the performance.  I need flexibility to create formulas and use dax so I used an import query in sql server.