DAX Query Use Cases Brett Powell BI Consultant

DAX Query Use Cases Brett Powell BI Consultant, Author

What is a DAX Query? • DAX Measures • Scalar values • DAX Queries • Table values • EVALUATE clause • Table parameter to measure DAX Measures in Power BI • DAX Query Authoring Tools • SQL Server Management Studio • DAX Studio DAX Queries in SSMS

Leverage DAX Queries Across Tools & Scenarios • User: custom table for their tool of choice or need • Scenario: custom table for individual report(s) or isolated use case • Store and reuse queries across data models (e. g. Dev/Prod) Report Projects in Visual Studio Reports in Report Builder BI Dev Tables in Excel Reports Data Model Analysis Services Database *Power BI Dataset (Premium) Power BI Report Power User SQL Server Management Studio DAX Studio BI Admin & Dev

Use Cases for DAX Queries 1. Paginated Reports in Power BI Report Server • or SQL Server Reporting Services (SSRS) 2. Drillthrough Expressions in Analysis Services • Detail Rows in Analysis Services 2017+ 3. 4. Performance Tuning • Capture and analyze queries from Power BI • Test performance of DAX measures Embed Queries (Tables) in DAX Measures • Query or queries drive measure results 5. Data Model Tables (Import Mode) • Analysis Services or Power BI 6. Excel Data Tables • Query local or external data model 7. Custom Application • DAX queries against Analysis Services 8. Data Model Troubleshooting • Missing rows, missing values 9. Calculated Tables in Power BI and Analysis Services and more….

Introductory Examples

Use Case: Excel Table via Custom ODC • Analyze in Excel or Power BI Publisher for Excel • Obtain ODC (Office Data Connection) to source model • Embed DAX Query in ODC File • Optionally embed multiple queries in Excel 1. Custom ODC 2. Retrieve Query as Table 3. New Query in Excel

Use Case: Ad Hoc Analysis; Troubleshooting • How many rows per table? • What’s the distribution of rows by date? Row Counts • Any missing rows or values? • Find the details of any exceptions? Rows with Blank Values Row Counts in SSMS

Use Case: Import Analysis Services to PBI Desktop 1. Single Table • Simplified self-service reporting via aggregated and filtered table • Pass DAX query from Power BI Desktop 2. Self-Service Model Table in Power BI Desktop • Merge Analysis Services data with other data source • DAX and M (Power Query) queries • Support limited self-service (sandbox) • Parameterized DAX query Query Dependencies in Power BI Desktop

DAX Query Structure and Functions

Common DAX Table Functions CALCULATETABLE SUMMARIZECOLUMNS FILTER VAR ALL INTERSECT VALUES EXCEPT DATATABLE UNION ADDCOLUMNS DISTINCT TOPN NATURALINNERJOIN SELECTCOLUMNS SUMMARIZE CROSSJOIN GENERATE CALCULATETABLE FILTER SUMMARIZECOLUMNS Table

DAX Query Structure and Examples • DEFINE (Optional) • Measures or Variables • EVALUATE (Required) • Table or Table Expression • ORDER BY (Optional) • START AT (Optional) Full DAX Query Syntax DAX Query Results: SQL Server Management Studio (SSMS)

DAX Query Examples 1. Select Columns • Relationships implied 2. Filter Conditions • CALCULATETABLE • FILTER, TOPN 3. Grouping Columns • SUMMARIZECOLUMNS() • SUMMARIZE() 4. Aggregations DAX Query Example • Use measures of model • Optionally create measures for query DAX Query Results: SQL Server Management Studio (SSMS)

DAX Queries in Power BI Report Server Reports

Paginated Reports • Why paginated reports? • Printer-friendly, multi-page reports • Leverage SSRS experience and reports • Migrate source of reports to Analysis Services • Mature, robust report design features • Development Tools: Paginated Report • Report Builder • SQL Server Data Tools (SSDT) Report Project in SQL Server Data Tools (SSDT)

Paginated Report Examples 1. Internet Sales Analysis • Standard paginated report (. RDL) 2. First Year Customer Report • Pages of customer details • Printer-friendly Paginated Report 3. Reseller Analysis • Reseller Details linked report • Drillthrough action via parameter 4. Internet Sales User Analysis • Parameters for interactive filtering • Single and multi-value parameters Linked Paginated Report

Embedding DAX Queries in Measures

DAX Queries in Measures Examples 1. Filter measure by table • FILTER and CALCULATETABLE 2. Multi-table OR conditions • CROSSJOIN • SUMMARIZE 3. Multiple Sets of Customers • UNION • NATURALINNERJOIN 4. Multiple Sets and Filters • FILTER, SUMMARIZE, and • CALCULATETABLE

Thank You

Contact Information Brett Powell Brett. Powell@Frontline. Analytics. net Linked. In Twitter: @Brett. Powell 76 Blog: Insight Quest Amazon: Author Page