Efficient Power BI Optimizing Queries Calculations About Us

Efficient Power BI Optimizing Queries & Calculations

About Us Christopher Keslin, Partner, Lachesis LLC. • Worked at a global scale in custom software development and Microsoftware integrations • Current focus in data analytics, C# development, and cybersecurity John Bulger, Data Scientist, Lachesis LLC. • Previously built predictive models for Altria, a Fortune 200 company, at Leo Burnett in Chicago • Passionate about data visualization and Python/R for data science

Source Data (SQL) Power BI Dataflow M/Power Query DAX

Direct Query vs. Import

Direct Query in a Nutshell When to use? • Data is changing frequently, and near ‘real-time’ reporting is needed • Handling very large data, without the need to pre-aggregate • Data sovereignty restrictions apply • The source is a multidimensional source containing measures (such as SAP BW) Limitations/Issues • Refresh time depends on source • Can put undue strain on servers • Costs can be higher (cost/query in certain cloud platforms) • Date/time-intelligence features unavailable or reduced • Certain DAX functions and Power Query transformations unavailable

M vs. DAX Power Query or Formulas: How to Choose?

M – Power Query Language

How to Choose M • Calculates on imports/refresh • Preferred choice for fixed calculations, conditionals, and data transformations • Examples: • Datetime conversions • Simple Conditional Columns on big datasets • Date dimensions • String manipulations • Large table duplication DAX • Calculates anytime visuals are interacted with • Works well for "easy" or dynamic calculations • Necessary for measures • Examples: • Dynamic measures such as percentages intended to change with visual • Simpler conditionals and columns on smaller data

DAX: Columns or Measures

Post M World Calculated Columns Measures • Static • Can be used to establish relationships • Used in row-confined functions • Memory intensive at load, but not as much when interacting • Think of as "Aggregate Functions" • Allow for flexibility in visuals and filtering • Constant recalculation during interaction

Example Cases vs.

Source Data (SQL) Efficient Order of Operations – Explore, then Optimize! M/Power Query DAX

Time for Questions! Data mining, SQL, M, DAX, R, Python, Visuals, Deployment. .