skills.rest
microsoft
microsoftOFFICIAL
Explore all skills by this author →
Website𝕏@OpenAtMicrosoftGitHub

sql_to_dax

Translate SQL aggregation expressions into semantically equivalent DAX measures for Power BI.

571|192|Updated May 16, 2024
Search Tags:#translation#aggregation#sql#DAX#Power BI#measures#Semantic Model
One-click install
npx skills add https://github.com/microsoft/semantic-link-labs --skill sql-to-dax
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql_to_dax
Source: https://github.com/microsoft/semantic-link-labs/tree/main/.claude/skills/sql_to_dax
Command: npx skills add https://github.com/microsoft/semantic-link-labs --skill sql-to-dax

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Converting analytical SQL queries into DAX measures is error-prone because SQL is row-set based while DAX is filter-context based. This Skill provides systematic translation rules that preserve business semantics while producing valid, best-practice Power BI DAX.

Core Features & Use Cases

  • Aggregate Translation: Converts SUM, AVG, COUNT(DISTINCT), and arithmetic-inside-aggregate patterns into scalar aggregations or iterator functions like SUMX and AVERAGEX.
  • Window Function Handling: Translates rolling windows (ROWS BETWEEN N PRECEDING) into CALCULATE with DATESINPERIOD, and unbounded OVER() windows into iterators over ALL(table).
  • Safe Division & Conditionals: Rewrites NULLIF division patterns as DIVIDE and CASE WHEN logic as CALCULATE with filters.
  • Use Case: A data engineer migrating Snowflake KPI queries to a Power BI semantic model uses this Skill to convert expressions like SUM(revenue - cost) / NULLIF(SUM(revenue), 0) into properly qualified DAX measures using DIVIDE and SUMX.

Quick Start

Translate this SQL expression into a DAX measure: SUM(fact_sales.price * fact_sales.quantity) / NULLIF(SUM(fact_sales.quantity), 0).

Frequently Asked Questions about sql_to_dax

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I convert SQL SUM with arithmetic to DAX?▼

When a SQL aggregate contains arithmetic like SUM(price * quantity), translate it to the iterator form SUMX('table', 'table'[price] * 'table'[quantity]). A single bare column inside SUM stays as the scalar form SUM('table'[column]).

How to translate SQL window functions like SUM OVER into DAX?▼

Rolling windows with ROWS BETWEEN N PRECEDING become CALCULATE wrapping the aggregation with a DATESINPERIOD filter anchored on MAX of the order-by date column. Unbounded OVER() windows translate to iterator functions over ALL('table').

What is the DAX equivalent of SQL NULLIF division?▼

SQL patterns like SUM(a) / NULLIF(SUM(b), 0) translate to DIVIDE(SUM('table'[a]), SUM('table'[b])). DIVIDE handles divide-by-zero safely, so the NULLIF guard is no longer needed.

Does DAX support referencing columns from multiple tables in one measure?▼

Yes, but the iterator table must be the many side of the relationship, typically the fact table. Columns from the one side, such as dimension attributes, must be wrapped in RELATED('dim'[column]) inside the iterator expression.

Why should SQL CASE WHEN inside COUNT DISTINCT become CALCULATE in DAX?▼

DAX has no direct CASE-inside-aggregate equivalent, so conditional distinct counts use CALCULATE(DISTINCTCOUNT('table'[col]), filter condition). This moves the conditional logic into DAX's filter context, preserving the original semantics.

Related Skills

Explore More Data & Analytics Skills→
yu-iskwyu-iskw
3

Read Metrics

List and inspect metric definitions and aggregation logic in Lightdash.

Community
Basic
data-goblindata-goblin
887

paginated-report

Author, validate, publish, and render Power BI paginated reports in RDL format.

Community
Advanced
data-goblindata-goblin
887

semantic-model

Designs, builds, refreshes, and audits Power BI tabular semantic models via the te CLI.

Community
Advanced
data-goblindata-goblin
887

dax

Diagnose and optimize slow DAX queries in Power BI semantic models using trace analysis.

Community
Advanced