To DAX or to SQL, that’s the Q.

Sometimes I feel like Sheldon when I need to decide between query/transformation technologies…

TL:DR

• Heavy lift by SQL, lightweight calculations in semantic layer by Dax.
• Avoid the temptation of DAX, do the SQL instead.

So I was working with Microsoft’s new Azure Fabric, and I got lost. So many options. Luckily, someone at Microsoft got lost like me and built a nice documentation article. Not only one but two:

Fabric decision guide - copy activity, dataflow, or Spark - Microsoft Fabric

Fabric decision guide - choose a data store - Microsoft Fabric

I like their consolidated tools, plus you can skip local client application installation, pure web/SaaS solution. But there are so many choices for a unified-SaaS product. Here is another potential new article Microsoft may be interested to write: When to model in DAX and when to SQL?

Let’s dance the DAX dance

DAX is a formula language used primarily in Microsoft Power BI, Power Pivot, and Power Query. It is designed to work with tabular data models and is particularly useful for creating calculated columns, measures, and complex calculations. DAX provides powerful functions and operators that enable users to perform advanced calculations and aggregations on data. It is especially effective for analyzing multidimensional data and creating interactive visualizations.

So, it runs only in Microsoft environment: PowerBI, SSIS, etc.

Taste like excel formulas to me.

Simple calculations sometimes need coding: Running sum? Window Functions?

Note that the Window function is something new to DAX. It was introduced in Dec 2022. 2–0–2–2. It only took 13 years.

Here a sample

Previous row value =
CALCULATE (
    [RunningSum],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date]
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date] < SELECTEDVALUE ( 'Table'[Date] ) )
            )
    )
)

What about SQL (See-Ku-Lí)

SQL runs in any database, and the window function statement looks clear to me.

SELECT 
    [PartitionColumn],
    [ValueColumn],
    LAG([ValueColumn]) OVER (PARTITION BY [PartitionColumn] 
    ORDER BY [OrderColumn]) AS Last_Value
FROM 
    [YourTable]

Stay tuned for more insightful articles and consult with our experts at Blue Orange Digital to unlock the full potential of your data-driven initiatives.

Contact us today to learn more about how Blue Orange Digital can help you navigate the complexities of data movement and streamline your data management processes.