Add support for the Data Analysis Expressions (DAX) Library

[Trace-Elements0]

It would be very helpful in the development and testing of DAX formulas and expressions for Power BI Reports, Power BI Models, Analysis Services, and Power Pivot in Excel data models.

I'd like for the Data Analysis Expressions (DAX) library to become a supported language by Polyglot notebooks. Ideally the notebook runs DAX code and displays the results.

There are two syntax styles used depending on where the DAX formulas are being evaluated. In reporting clients like Power BI and Excel the formulas are evaluated and placed in DAX Queries behind the scenes so your formulas are simply defined into named identifiers, so the formulas don't require statements like EVALUATE. However, in tools like DAX Studio, SSMS, and DAX.do must define the entire query so EVALUATE is required. I'm not sure which syntax would be best to support but I'll include both in the samples below.

Code Samples

Create a table

Uses the table constructor syntax to create a table.

Usage - Power BI Desktop Formula Bar Syntax

multi_column_table = {
    (1.5, DATE(2017, 1, 1), CURRENCY(199.99), "A"),
    (2.5, DATE(2017, 1, 2), CURRENCY(249.99), "B"),
    (3.5, DATE(2017, 1, 3), CURRENCY(299.99), "C")
}

Usage - Query Syntax

EVALUATE {
    (1.5, DATE(2017, 1, 1), CURRENCY(199.99), "A"),
    (2.5, DATE(2017, 1, 2), CURRENCY(249.99), "B"),
    (3.5, DATE(2017, 1, 3), CURRENCY(299.99), "C")
}

Polyglot Notebook Result

Value1	Value2	Value3	Value4
1.5	1/1/2017 12:00:00 AM	$199.99	A
2.5	1/2/2017 12:00:00 AM	$249.99	B
3.5	1/3/2017 12:00:00 AM	$299.99	C

Create table using DAX UNION Function

The first two tables use the constructor syntax similar to the first example but place the results in variables and the actual result table is produced by evaluating the DAX UNION function to create a join table from the pair.

Usage - Power BI Desktop Formula Bar Syntax

Western Region Employees =
    // define two table variables
    VAR NorthwestEmployees = {"John Doe"}
    VAR SouthwestEmployees = {"Jane Doe"}
    // combining tables
    RETURN UNION(NorthwestEmployees,SouthwestEmployees)

Usage - Query Syntax

DEFINE
    // define two table variables
    VAR NorthwestEmployees = {"John Doe"}
    VAR SouthwestEmployees = {"Jane Doe"}
 // combining tables
EVALUATE
    UNION(NorthwestEmployees,SouthwestEmployees)

Polyglot Notebook Result

Value

John Doe Jane Doe

Currently to DAX I can use DAX Studio, which requires I open a .pbix file in Power BI Desktop and connect to the SQL Server Analysis Services instance included in the locally ran .pbix file. Another option is using DAX.do but neither option has the portability benefits and markdown support that notebooks do. Being able to walk people through how you came to a solution and sharing it would be very helpful.