Sql dax

Sql dax DEFAULT

DAX for SQL Folks: Part II - Translating SQL Queries to DAX Queries

Introduction

In response to my approach in the popular MDX Guide for SQL Folksseries, I am using SQL as a good frame of reference for starting or developing a new approach for improving your Data Analysis Expression(DAX) language learning experience. This is useful for developers starting to learn the DAX language to more advanced developers who have struggled to adjust to the language coming from a SQL background.

In Part I of the series, we discussed some very important Data Visualization and Tabular Model concepts that are essential to the DAX learning process, esp. when coming from a SQL background;

  1. First, we discussed the importance of learning and applying Data Visualization techniques. We learned that applying these can increase one's potential to effectively communicate decision making insights to drive action. There are many books available that teach this art and science of analytical storytelling.
  2. We also learned the benefits of interactive visualization that comes with tools like Power BI. Interactive visualization tools take the concept of data visualization a step further by using data modeling and technology to enable end-user to drill down into visuals to fully explore and analyze data.
  3. We examined the key similarities and differences between and a SQL Server Database and the Tabular Model that power T-SQL and DAX respectively. We learned that:
    • In both databases the basic elements are tables, but in a SQL Server Database, tables are isolated units whilst in Tabular Models, tables are all joined into one physical data model.
    • Because tables are left joined to other tables on the many-to-one side, tables in Tabular Models are essentially Extended Tables.
    • Understanding the Extended Tables and other Tabular Models concept is key. It will help one to grasp the effects of DAX relationships,filter propagation, interactivity and their effects on the DAX formulas and expression that you will write.
  4. Finally, we learned that, even though both DAX and SQL languages can achieve the same query results, SQL is a Declarative Language whilst DAX is a Purely Functional Language. Knowing and always keeping this last point in mind and also understanding how functional language syntax is constructed makes transitioning from SQL to DAX a lot easier.

If you have not done so yet, please refer to Part I, especially for the concept outlined in bullet points 3 and 4, they form the foundation of this learning process.

In Part II we are going to continuing to learn DAX by solidifying our understanding of its functional nature compared to the declarative nature of SQL. We will learn how the pre-built relationship between tables in a Tabular Model affects DAX language as opposed to using join statements in SQL simply because tables in SQL Server databases are not joined to each other. We will do this by simply translating SQL queries to DAX queries.

Part II: SQL Queries vs DAX  Queries

We are going to make the initial learning process simple and straight forward by comparing SQL and DAX queries. We will introduce SQL clauses and syntax first followed by their DAX equivalent. We will look at how the SQL clauses in the Logical SQL query processing steps below translated into DAX as a functional language.

 Logical SQL query processing steps

(4) SELECT () DISTINCT () TOP(<top_specification>) () <select_list> (1) FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate> (2) WHERE <where_predicate> (3) GROUP BY <group_by_specification> (5) ORDER BY <order_by_list>;

By following and understanding the translation of the Logical SQL query processing steps above into functional DAX syntaxes, you would have learned a lot about what you need to know about DAX. Most resources on this topic do not teach or emphasize the functional nature and syntax of DAX so learners leave without a very key element. We are going to emphasize this in this section with Summarized Functional Syntaxes that ignores non-functional arguments.

As we translate SQL to DAX functional syntax, one should understand that logically the inner functions are processed first and passed on to the next ones in the function chain, as opposed to the numbered SQL query processing steps shown above.

Database Installation

For those who want to follow the examples by executing both the SQL and DAX queries you can do that by installing the AdventureWorksDW SQL Server and SSAS Tabular Model sample databases.

  • To download and install the Data Warehouse versions of the AdventureWorks SQL Server sample database you can follow the example in this link.
  • To download and install the AdventureWorks Tabular Model sample database you can follow the examples in the links below.
    1. Download
    2. Install

SQL and DAX Client Query Tools

Most readers on this forum should be familiar with SSMS. SSMS is the primary client tool for querying SQL Server Databases. After installing the AdventureWorks Tabular Model sample database , one can also connect to the database and run DAX queries using SSMS by following the steps in figure 1 below.

  1. First, click on the DAX query button in SSMS.
  2. This will launch the screen that lets you select and connect to the SSAS Server with the Tabular Database you install above.
  3. Make sure you select the database you want to query
  4. Proceed to write and execute your DAX queries

 

                                                   figure 1:  Showing how to connect to SSAS Tabular Databases and write DAX queries.

However, if you are going to run a lot of ad-hoc DAX queries and test DAX calculations and expressions, then I suggest you install and use DAX Studio since it offers more DAX features, like formating. Please visit http://daxstudio.org to read the full documentation and download the latest release of DAX Studio.

DAX Table and Column Name Syntax

It is important to know the general format for referencing table and columns in DAX, as shown below;

            'Table Name'                   //  Table  reference                   'Table Name'[Column Name]      //  Column reference

If the table name has no spaces, the single quotes can be omitted, so the syntax looks like below:

                TableName[Column Name]

In this series, we are going to use the quoted version whether the table name has spaces or not.

DAX Functions Reference

We will encounter new DAX functions as we go along. For these discussions, a few key ones will be partially introduced.  For an in-depth look at any function, one can follow the links provided below.

These function references provide detailed information including syntax, parameters, return values, and examples for each of the functions that will be used DAX queries and formulas in this series.

 SQL SELECT FROM Clauses vs DAX EVALUATE  Function

Just like you need the SELECT FROM statement in SQL to query tables in a Relational Database, in DAX if you need the EVALUATE function to query the data in a tabular model. Let's start with a simple statement by querying the DimProduct Table in both databases with SQL and DAX.

Listing 1:

SQL:

Select * From DimProduct

DAX:

EVALUATE('DimProduct')

Both queries in Listing 1 above return all the rows from the DimProduct table. From the DAX query, we see the use of the EVALUATE function, which is the only required function for querying in DAX. Note thatEVALUATE was not used with any column projection function so the effect is similar to SELECT * FROM.

Intro to the EVALUATE Function

To query data directly in a tabular model, you need the EVALUATE function. The EVALUATE function is used to return the result set as a table. It is the equivalent to using SELECT FROM statement in T-SQL to return columns and rows from a table.

     EVALUATE('table name')

In this expression 'table name' refers to the name of a table, a table function, or an expression that returns a result set.

Note: Every time you have a DAX function that accepts a table expression as an argument, you can write the name of a table in that parameter, or you can write a function or an expression, that returns a table.

Let's explore the functional nature of DAX a bit further.  In the next logic, we will query the top 5 records from the DimProduct table as shown in Listing 2 below.

Listing 2:

SQL:

SELECT TOP(5) * FROM DimProduct

DAX:

EVALUATE(TOPN(5,'DimProduct'))

TOPN is DAX equivalent of the SQL TOP function, as you can see from Listing 2, the TOPN function is just nested within the EVALUATE function as below, illustrating the functional syntax of DAX.

Summarized Functional Syntax:

EVALUATE(TOPN())

 

SQL WHERE Clause vs the DAX FILTER Function

The DAX FILTER Function is the function used to achieve the effects of the WHERE clause in the SQL query logical steps.

In the following example, we will write a query to restrict the top 5 records from the DimProduct table to where the color of the product is black and the status of the product is marked as current. The SQL and DAX equivalent of this logic is as shown in Listing 3 below.

Listing 3:

SQL:

SELECT TOP(5) * FROM DimProduct WHERE [DimProduct].[Color]='Black' AND [DimProduct].[Status] ='Current'

DAX (unformatted):

EVALUATE(TOPN( 5, ( FILTER( 'DimProduct', 'DimProduct'[Color] = "Black" && 'DimProduct'[Status] = "Current" ) ) ) )

DAX(formatted):

EVALUATE ( TOPN ( 5, ( FILTER ( 'DimProduct', 'DimProduct'[Color] = "Black" && 'DimProduct'[Status] = "Current" ) ) ) )

 

Two options of the DAX query are shown in Listing 3: an unformatted single line and a more functional, formatted version. The versions are to illustrate the point that, no matter how complex it is, a DAX expression is like a single function call as shown in the functional syntax below.

Summarized Functional Syntax:

EVALUATE(TOPN((FILTER())))

The complexity of the DAX code comes from the complexity of the expressions that one uses as parameters for the outermost function.

DAX Formatting

As formulas start to grow in length and complexity, it is extremely important to format the code to make it human-readable. Functional language formatting follow certain rules and could be a  time-consuming operation. The Folks at SQLBI created a free tool can that transform your raw DAX formulas into clean, beautiful and readable formatted code. You can find the website at www.daxformatter.com. On the website, you can also learn the syntax rules used to improves the readability DAX expressions.

Intro: FILTER Function

The filter function is one of the most important DAX functions you will encounter. The FILTER function, however, has a very simple role: it is an iterator table function. It gets a table, iterates over the table and returns a table that has the same columns as in the original table, but contains only the rows that satisfy a filter condition applied row by row.

The syntax of FILTER is the following;

FILTER (<table>, <condition>)

FILTER iterates the <table> and, for each row, evaluates the <condition>, which is a Boolean expression.When the <condition> evaluates to TRUE, the FILTER returns the row; otherwise, it skips it.

Note From a logical point of view, FILTER executes the <condition> for each of the rows in <table>. However, internal optimizations in DAX might reduce the number of these evaluations up to the number of unique values of column references included in the <condition> expression. The actual number of evaluations of the <condition> corresponds to the “granularity” of the FILTER operation. Such a granularity determines FILTER performance, and it is an important element of DAX optimizations.

SQL ORDER BYClause vs DAX ORDER BY Parameter

The DAX Order By keywords or optional parameter is similar to the SQL Order By keywords. The query in Listing 4 below shows how the previous result set from the DimProduct table is ordered by EnglishProductName.

Listing 4:

SQL:

SELECT TOP(5) * FROM [DimProduct] WHERE [DimProduct].[Color]='Black' AND [DimProduct].[Status] ='Current' ORDER BY [DimProduct].[EnglishProductName]

DAX:

EVALUATE ( TOPN ( 5, ( FILTER ( 'DimProduct', 'DimProduct'[Color] = "Black" && 'DimProduct'[Status] = "Current" ) ) ) ) ORDER BY 'DimProduct'[EnglishProductName]

Note that in DAX ORDER BY is not a function; it is rather an optional parameter of EVALUATE function.

Summarized Functional Syntax:

EVALUATE(TOPN((FILTER()))) ORDER BY

SQL Select_list vs DAX Projection Functions

In SQL, the select_list enables you to outline the columns you want in your query result set, as in;

SELECT column1, column2,…. FROM

In DAX there are a couple of functions you could use to project table columns to achieve the same results as SQL  select_list. For DAX querying purposes we will use the very efficient SUMMARIZECOLUMNS function which has been designed to be the “one function fits all” to run queries.

The example that follows shows a simple query to illustrate the use of SUMMARIZECOLUMNS function. The queries show how to project EnglishProductName and Color columns from the DimProduct table.

Listing 5:

SQL:

SELECT [EnglishProductName] , [Color] FROM DimProduct

DAX  (Option 1):

EVALUATE ( SUMMARIZECOLUMNS ( 'DimProduct'[EnglishProductName], // column name 'DimProduct'[Color], // column name 'DimProduct' // Base Table name ) )

DAX (Option 2) :

EVALUATE ( SUMMARIZECOLUMNS ( 'DimProduct'[EnglishProductName], // column name 'DimProduct'[Color] // column name ) )

As we can see from the listing above there are two options of the query for DAX, Option 1 with the name of the table and Option 2 without the name of the table.

Note that option 2 works and it goes to show that the table expression is optional. However, when selecting from multiple tables you must specify the base table to use for the join operation, if not you obtain a cross join. On the other hand, if there is an aggregation expression in your query, the DAX engine would infer the base table from the aggregation expression. In this series, we are going to use the first option by always specifying the base table whether projecting from one or more tables.

Let's try a more complex logic by selecting columns in our previous queries from Listing 4 as shown in Listing 6 below.

Listing 6:

SQL:

SELECT TOP(5) [EnglishProductName] , [Color] FROM DimProduct WHERE DimProduct.[Color]='Black' AND DimProduct.[Status] ='Current' ORDER BY DimProduct.[EnglishProductName]

DAX:

EVALUATE ( TOPN ( 5, ( SUMMARIZECOLUMNS ( 'DimProduct'[EnglishProductName], 'DimProduct'[Color], FILTER ( 'DimProduct', 'DimProduct'[Color] = "Black" && 'DimProduct'[Status] = "Current" ) ) ) ) ) ORDER BY 'DimProduct'[EnglishProductName]

 

Summarized Functional Syntax:

EVALUATE(TOPN(SUMMARIZECOLUMNS(FILTER()))) ORDER BY

SQL GROUP BY Vs DAX Aggregation

Unlike SQL, where you have to explicitly state the group by clause, in DAX the feature is built into SUMMARIZECOLUMNS and other column projection functions. The group by feature is forced by the introduction of an aggregation function within the SUMMARIZECOLUMNS function as shown in Listing 7 below.

Listing 7:

SQL:

SELECT salesordernumber, Sum([salesamount])AS 'SumOfSales' FROM FactResellersales GROUP BY salesordernumber

DAX:

EVALUATE ( SUMMARIZECOLUMNS ( 'FactResellerSales'[SalesOrderNumber], 'FactResellerSales', "SumOfSales", SUM ( 'FactResellerSales'[SalesAmount] ) ) )

 

Summarized Functional Syntax:

EVALUATE( SUMMARIZECOLUMNS( SUM() ) )

As we can see from the DAX logic in Listing 7 above, by the mere introduction of the SUM function,  SUMMARIZECOLUMNS function knows to group by the projected columns. Always remember that you must specify table expression argument after the columns you projecting and before the aggregated or calculated columns.

Intro: SUMMARIZECOLUMNS Function

We've learned that SUMMARIZECOLUMNS is an extremely powerful query function with all the features needed to execute a query.  It returns a summary table over a set of groups. SUMMARIZECOLUMNS lets you specify:

  • A set of new columns to add to the result.
  • A set of columns used to perform Group-By, with the option of producing subtotals. SUMMARIZECOLUMNS automatically groups data by selected columns. The result is equivalent to SQL Select Distinct.
  • A set of filters to apply to the model before performing the group-by.
  • You must specify the table expression argument after the columns you projecting and before the aggregated or calculated columns.
  • SUMMARIZECOLUMNS automatically removes from the output any row for which all the added columns produce a blank value.
  • In SUMMARIZECOLUMNS you can add multiple filter tables, which could be useful for queries applied to complex data models with multiple fact tables.
  • Because of this aggregating feature SUMMARIZECOLUMNS always return a distinct number of record

SQL Joins vs  DAX Relationships

In Part I, we learned that in SQL Server databases, tables are isolated units whilst in Tabular Models, tables are all joined into one physical data model. When you build a tabular model, the relationships you define between tables at design time get established as actual relationships because the DAX engine joins all the tables together with a left join. So essentially,  tabular model tables are extended tables. Please refer to Part I of the series where these concepts are well explained.

What these concepts mean is that unlike SQL where you specify joins within the query, DAX uses an automatic LEFT OUTER JOIN in the query whenever you use columns related to the primary table (the table on the left side of the Join.

Let's see how these concepts play out in the SQL and DAX queries. In the next listing, we are going to select from two tables FactResellerSales and DimReseller with a query that outlines the Reseller name and related total sales amount.

Listing8:

SQL:

SELECT resellername, Sum (salesamount) AS sumofsales FROM factresellersales LEFT JOIN dimreseller ON factresellersales.resellerkey = dimreseller.resellerkey GROUP BY dimreseller.resellername ORDER BY dimreseller.resellername

DAX:

EVALUATE ( SUMMARIZECOLUMNS ( 'DimReseller'[ResellerName], 'FactResellerSales', "SumOfSales", SUM ( 'FactResellerSales'[SalesAmount] ) ) ) ORDER BY 'DimReseller'[ResellerName]

As we can see from Listing 8 above, in SQL we explicitly use a Join statement. On the other hand, because the DAX engine knows the existing relationship between the FactResellerSales base table and the DimReseller Tables in the model, we can project any column from the two tables without an explicit join statement and seen in the DAX version of the query.

Similarly, if we want to filter the result set above to only Resellers in North America, in SQL we need to explicitly join the DimSalesTerritory in other to filter the SalesTerritoryGroup as shown in  Listing 9  below. On the other hand, in the DAX version, we just place a filter on the DimSalesTerritory Table, and we are good to go. The DAX engine knows the existing relationship between the FactResellerSales base table and the DimSalesTerritory Table in the model.

Listing 9:

SQL:

SELECT Resellername, Sum (salesamount) AS sumofsales FROM factresellersales LEFT JOIN dimreseller ON factresellersales.resellerkey = dimreseller.resellerkey LEFT JOIN dimsalesterritory ON factresellersales.salesterritorykey = dimsalesterritory.salesterritorykey WHERE dimsalesterritory.salesterritorygroup = 'North America' GROUP BY dimreseller.resellername

DAX:

EVALUATE ( SUMMARIZECOLUMNS ( 'DimReseller'[ResellerName], FILTER ( 'DimSalesTerritory', 'DimSalesTerritory'[SalesTerritoryGroup] = "North America" ), 'FactResellerSales', "SumOfSales", SUM ( 'FactResellerSales'[SalesAmount] ) ) )

Functional Syntax:

EVALUATE( SUMMARIZECOLUMNS ( FILTER(),SUM() ) )

SQL Sub Queries Vs DAX Sub Queries

Now, let's modify the query in Listing 9 using a subquery to identify Resellers in North America with sales of more than K. Listing10 below shows the SQL and DAX version of the subquery.

Listing

SQL:

SELECT resellername, sumofsales FROM ( SELECT resellername, Sum (salesamount) AS sumofsales FROM factresellersales LEFT JOIN dimreseller ON factresellersales.resellerkey = dimreseller.resellerkey LEFT JOIN dimsalesterritory ON factresellersales.salesterritorykey = dimsalesterritory.salesterritorykey WHERE dimsalesterritory.salesterritorygroup = 'North America' GROUP BY dimreseller.resellername ) AS subq WHERE subq.sumofsales > ORDER BY resellername

DAX:

EVALUATE FILTER ( SUMMARIZECOLUMNS ( 'DimReseller'[ResellerName], FILTER ( 'DimSalesTerritory', 'DimSalesTerritory'[SalesTerritoryGroup] = "North America" ), 'FactResellerSales', "SumOfSales", SUM ( 'FactResellerSales'[SalesAmount] ) ), [SumOfSales] > ) ORDER BY DimReseller[ResellerName]

 

Functional Syntax:

EVALUATE( FILTER( SUMMARIZECOLUMNS( FILTER(), SUM() ) ) )

As we can see from the DAX version, a subquery is just a matter of nesting query within a Filter function. In the code above, the subquery that retrieves ResellerName and SumOfSales is later fed into a FILTER function that retains only the rows where SumOfSales is greater than K.

As you get conversant with DAX's functional nature and logical query steps, you will discover that selecting from multiple tables and using subqueries is much easier than in SQL.

Intro to DAX Query Variables and Query Measures

We've learned that EVALUATE is the function required to execute a DAX query. We are going to wrap up DAX queries by looking at DAX Query Variables and Query Measures. These are variables and calculations you can pre-define and use in your EVALUATE query statements.

A very useful side of DAX is that one can build more complex calculations on top of existing ones, that's what Query Measures and Query Variables lets you do in DAX queries. They enable you to test complex algorithms with queries by breaking them down into simpler reusable calculations.

The DEFINE keyword is the optional EVALUATE parameter needed to define Query Variables and Query Measures. Listing 10 below shows a simple example of how to define Query Variables and Query Measures and call them in an EVALUATE statement.

Listing

DEFINE VAR Profitlimit = MEASURE 'FactResellerSales'[profit] = SUM ('FactResellerSales'[SalesAmount] ) - SUM ('FactResellerSales'[TotalProductCost]) EVALUATE FILTER ( SUMMARIZECOLUMNS ( 'DimDate'[CalendarYear], 'FactResellerSales', "Profit", 'FactResellerSales'[profit] ), [Profit] > Profitlimit ) ORDER BY 'DimDate'[CalendarYear]

In Listing 10 above, the DEFINE keyword has been used to define a Query Variables with the VAR keyword and Query Measures with the MEASURE keyword. The variable and Measure are then used in the EVALUATE query logic.

Note that in the definition of the Query Measures, you must specify the table that hosts the measure. In the example, we are hosting the measure Profit in the FactResellerSales.

In the next example, let's assume you an Analyst at Adventureworks, you get a request call to provide the Profit Margin of products sold by Resellers. You pretend you know what is being asked of you, and then you google Profit Margin to come up with the formulas below.

The logic Listing 11 below shows how you translate the algorithm with DAX Queries by breaking it down into simpler calculations using Query Measures.

Listing

DEFINE MEASURE 'FactResellerSales'[ProductSales] = SUM ('FactResellerSales'[SalesAmount]) MEASURE 'FactResellerSales'[CostOfProductSold] = SUM ('FactResellerSales'[TotalProductCost]) MEASURE 'FactResellerSales'[Profit] = [ProductSales] - [CostOfProductSold] MEASURE 'FactResellerSales'[ProfitMargin] = DIVIDE ( [Profit], [ProductSales] ) EVALUATE SUMMARIZECOLUMNS ( 'DimProduct'[EnglishProductName], 'FactResellerSales', "Profit Margin", 'FactResellerSales'[ProfitMargin] )

As you can see from Listing 11, two Query Measures ProductSales and CostOfProductSold defined and hosted on the FactResellerSales are then subsequently used in the Profit calculations. The ProfitMargin measure simply invokes and divide the Profit and ProductSales measures. Finally, using the EVALUATE function, the product names are projected with ProfitMargin measure.

In the final example, we are going to learn a trick that lets you create arbitrarily shaped filters to use in your queries. Let's say you are often asked to filter the query in Listing 11 above. E.g. you are often asked to provide ProfitMargin for specific scenarios, like particular Resellers in a particular geography for particular years, etc. Normally one will have to use filter functions to achieve these results (similar to how we translated the SQL WHERE Clause above). In DAX there is a function, called TREATAS, that provides a way to add arbitrarily shaped filters to your query making such requests easy to reproduce.

In the example in Listing 12 below, the previous query in Listing 11 has been modified to add an arbitrarily shaped filter using the TREATAS function.

Listing

DEFINE MEASURE 'FactResellerSales'[ProductsSales] = SUM ('FactResellerSales'[SalesAmount]) MEASURE 'FactResellerSales'[CostOfProductsSold] = SUM ('FactResellerSales'[TotalProductCost]) MEASURE 'FactResellerSales'[Profit] = [ProductsSales] - [CostOfProductsSold] MEASURE 'FactResellerSales'[ProfitMargin] = DIVIDE ( [Profit], [ProductsSales] ) EVALUATE SUMMARIZECOLUMNS ( 'DimDate'[CalendarYear], 'DimProduct'[EnglishProductName], 'FactResellerSales', // Arbitrary filter TREATAS ( { ( , "Progressive Sports" ) ,( , "Progressive Sports" ) }, 'DimDate'[CalendarYear], DimReseller[ResellerName] ), // End Arbitrary filter "Profit Margin", 'FactResellerSales'[ProfitMargin] )

In Listing 12  the query filters ProfitMargin for products specific to a Reseller named "Progressive Sports" for the years and The various filter parameters are passed to the query in Listing 11 by forming arbitrarily shaped filters with the function as shown below.

         // Start Arbitrary filter         TREATAS (                      {                          ( , "Progressive Sports" ),                          ( , "Progressive Sports" )                       },                       'DimDate'[CalendarYear], DimReseller[ResellerName]           ),         // End Arbitrary filter

Note that the approach uses the DAX Table Constructor that allows you to define an anonymous table directly in code. The parenthesis { } represents the virtual table in this case two columns and two rows as below.

When applied in the query it creates a virtual relationship between a virtual table defined with specific virtual values to the base table (the expanded version). The result of the virtual table expression is passed as filters to columns passed as arguments in the TREATAS function, in this case, CalendarYear and ResellerName. Remember that these columns are columns in the extended version of the FactResellerSales base table ( please refer to the Expanded Table section in Part I ).

As we can see the TREATAS function offer one of the best ways to implement a virtual relationship in DAX queries. This approach is very useful if you have a small number of unique values to propagate in the filter. You can read more on DAX Table Constructor here and on the TREATAS function here.

Intro: DEFINE keyword

DEFINE introduces a query definition section that allows one to define local entities like tables, columns, query variables, and query measures that are valid for all the following EVALUATE statements. There can be a single definition section for the entire query, even though the query can contain multiple EVALUATE statements.

     Query Variables

  • Variables can be any data type, including entire tables.
  • Query Variables cannot be used in Query Measures.

    Query Measures

  • In the definition of the measure, you must specify the table that hosts the measure.
  • Query measures are useful for two purposes:
    1. To write complex expressions that can be called multiple times inside the query.
    2.  They are useful for debugging and for performance tuning measures for instance before they are used in Power BI reports

Summary

After learning some fundamentals about DAX and the Tabular Model in Part I, we had set out to continue the learning process by understanding the functional nature DAX by simply translating SQL queries to DAX queries. We have accomplished that by translating all the causes in the SQL logical processing steps into DAX functional syntaxes.

Take-away

We learned that EVALUATE is the only required function to run a DAX query. Finally, we learned how to use the optional DEFINE parameter to define query variables and query measures that could be used as arguments or parameters within the body of an EVALUATE statement.

Essentially we were able to accomplish all DAX queries with three functions namely EVALUATE, SUMMARIZECOLUMNS and, FILTER.  By learning how these three functions arguments to the summarized functional syntax below one can accomplish most of the DAX queries we wrote in this section.

EVALUATE( SUMMARIZECOLUMNS( FILTER()  ))

Next

In the next installment of the series, we are going to take what we've learned from DAX queries into writing DAX calculations in the form of formulas that we will use in Power BI reports.

Sours: https://www.sqlservercentral.com/articles/dax-for-sql-folks-part-ii-translating-sql-queries-to-dax-queries

Many seasoned BI Reporting professionals would have used T-SQL extensively, either in the initial stages of their career as a database developer writing numerous lines of a stored procedure or as part of the BI report authoring role to generate historical reports using SSRS or Crystal reports. When they gradually move to use Power BI to create reports and dashboards, they get introduced to Power Query and DAX. This is exactly the stage where they get into a tough situation of deciding when they need to use T-SQL and when to use DAX or Power Query. 

It is a familiar situation that any Power BI expert now would vouch for as Power BI enables you to use expressions right from data source level to upmost level.

  • Data source (SQL view and/or custom SQL)
  • Power Query
  • DAX calculated columns
  • DAX measures (the highest level)

The best way to tackle DAX vs SQL confusion is to get better quipped in all the above capabilities. This way, it will be easier to use the best options based on the current task&#;s requirements.

The above approach is because of the varied nature of data transformation; it becomes harder to generalize the answer for the DAX vs SQL questions. 

When SQL is better than DAX

SQL is a structured query language, whereas DAX is a formula language used for data analysis purposes. When our data is stored in some structured database systems like SQL server management studio, MySQL, or others, we have to use SQL to fetch the stored data. We can&#;t directly compare SQL in parallel with DAX because it all depends on what we are trying to achieve. Sometimes, we need to prepare data that will use later on for Power BI desktop to build interactive dashboards or to built Tabular data models. For the data modeling, we need to feed data to these models, and that&#;s why we have to use SQL to prepare the data for further transformations.

In data analysis and business intelligence reporting, if we are using SSRS &#; SQL server reporting services, we have to use SQL to fetch the data and then display using SSRS by applying business logic.

When DAX is better than SQL

DAX is a newer language than SQL, and as we know that there is no direct comparison. In reporting, we cannot get realtime interactivity when we use SQL with SSRS, but we can get realtime interactivity when we use SQL with Power BI desktop. Different visuals in Power BI can interact with each other in realtime. We can solve complex business issues that need a lot of code & complexity in SQL compared to DAX.

DAX is not a language designed to fetch the data like SQL rather than used for data analysis purposes. Preliminary DAX is used in Power BI DAX, Power Pivot, and SQL server analysis services Tabular mode. In the beginning, learning DAX is not that easy; it needs a lot of patience to learn underlying concepts. Power BI DAX entirely based on row & columns. It would help if you had a solid understanding of row context and filter context to write DAX formulas to solve complex business issues.

Power BI DAX in practice

It is always a better and recommended approach to transform the data as close to the data source itself. For example, your data source is a relational database; then, it&#;s better to go with T-SQL. Being a flagship product from Microsoft, SQL server has gone through many improvements in the past three decades, with performance improvements being given top priority. Hence, it is only better to leverage the T-SQL skill that you have learned and taken advantage of SQL server&#;s performance offers high volume data crunching. 

If your data sources are flat (.csv) files or Excel documents, there should be any confusion, and Power Query can do the job for you in shaping and transforming data. The decision to choose DAX or Power Query can be tricky!

You can introduce a custom column either as DAX Calculate Columns or using Power Query, only if the performance is optimal. When you do this, the data model will treat and compress custom columns, just like regular columns.

It is now clear that you should look at calculated columns using DAX only when Power Query lacks features like ranking. Still, your requirement mandates using rankings, and when the transformations that are done using Power Query consume longer refresh times when a lookup happens between tables.

What about DAX Measures? Being unique in nature, these can only be implemented in DAX as opposed to custom columns, which can practically be implemented in any of the three layers. An excellent example of putting things in perspective would be to use an expression to reflect filters selected by end-users. This can only be a DAX Measure as they allow filter values to be evaluated and accessed at run time.

Categories Learn DAXTags DAX vs SQLSours: https://www.learndax.com/dax-vs-sql-when-to-use-dax-over-sql/
  1. Live love nails
  2. The blvd premier apartments
  3. Custom bmw e39

DAX queries

  • 4 minutes to read

With DAX queries, you can query and return data defined by a table expression. Reporting clients construct DAX queries whenever a field is placed on a report surface, or a whenever a filter or calculation is applied. DAX queries can also be created and run in SQL Server Management Studio (SSMS) and open-source tools like DAX Studio. DAX queries run in SSMS and DAX Studio return results as a table.

Before learning about queries, it's important you have a solid understanding of DAX basics. If you haven't already, be sure to checkout DAX overview.

Syntax

Keywords

EVALUATE (Required)

At the most basic level, a DAX query is an EVALUATE statement containing a table expression. However, a query can contain multiple EVALUATE statements.

Syntax

Arguments

TermDefinition
tableA table expression.

Example

Returns all rows and columns from the Internet Sales table, as a table.

DAX Evaluate statement

ORDER BY (Optional)

The optional ORDER BY keyword defines one or more expressions used to sort query results. Any expression that can be evaluated for each row of the result is valid.

Syntax

Arguments

TermDefinition
expressionAny DAX expression that returns a single scalar value.
ASC(default) Ascending sort order.
DESCDescending sort order.

Example

Returns all rows and columns from the Internet Sales table, ordered by Order Date, as a table.

DAX Evaluate order by statement

START AT (Optional)

The optional START AT keyword is used inside an ORDER BY clause. It defines the value at which the query results begin.

Syntax

Arguments

TermDefinition
valueA constant value. Cannot be an expression.
parameterThe name of a parameter in an XMLA statement prefixed with an character.

START AT arguments have a one-to-one correspondence with the columns in the ORDER BY clause. There can be as many arguments in the START AT clause as there are in the ORDER BY clause, but not more. The first argument in the START AT defines the starting value in column 1 of the ORDER BY columns. The second argument in the START AT defines the starting value in column 2 of the ORDER BY columns within the rows that meet the first value for column 1.

Example

Returns all rows and columns from the Internet Sales table, ordered by Sales Order Number, beginning at SO

DAX Evaluate order by Sales order number statement

Multiple EVALUATE/ORDER BY/START AT clauses can be specified in a single query.

DEFINE (Optional)

The optional DEFINE keyword defines entities that exist only for the duration of the query. Definitions are valid for all EVALUATE statements. Entities can be variables, measures, tables, and columns. Definitions can reference other definitions that appear before or after the current definition. Definitions typically precede the EVALUATE statement.

Syntax

Arguments

TermDefinition
tableNameThe name of an existing table using standard DAX syntax. It cannot be an expression.
nameThe name of a new measure. It cannot be an expression.
expressionAny DAX expression that returns a single scalar value. The expression can use any of the defined measures. The expression must return a table. If a scalar value is required, wrap the scalar inside a ROW() function to produce a table.
VARAn optional expression as a named variable. A VAR can be passed as an argument to other expressions.

Example

Returns the calculated total sales for years and , and combined calculated total sales for years and , as a table. The measure in the DEFINE statement, Internet Total Sales, is used in both Total Sales and Combined Years Total Sales expressions.

DAX Evaluate with measure defnition

Parameters in DAX queries

A well-defined DAX query statement can be parameterized and then used over and over with just changes in the parameter values.

The Execute Method (XMLA) method has a Parameters Element (XMLA) collection element that allows parameters to be defined and assigned a value. Within the collection, each Parameter Element (XMLA) element defines the name of the parameter and a value to it.

Reference XMLA parameters by prefixing the name of the parameter with an character. Any place in the syntax where a value is allowed, the value can be replaced with a parameter call. All XMLA parameters are typed as text.

Important

Parameters defined in the parameters section and not used in the <STATEMENT> element generate an error response in XMLA. Parameters used and not defined in the <Parameters> element generate an error response in XMLA.

See also

FILTER
SUMMARIZECOLUMNS
TREATAS
VAR

Sours: https://docs.microsoft.com/en-us/dax/dax-queries
DAX for SQL Folks - DAX as a Query Language

Introduction

In our SQLShack discussions over the past few weeks, we have dealt with a few of the more conventional SQL Server data manipulation techniques. Today we are going to be a bit more avant-garde and touch upon a subject dear to my heart.

With Power Bi becoming more and more important on the business side within major industries worldwide, it is not surprising that sooner or later every SQL programmer is going to have to learn and be able to ‘talk’ DAX.

In this article we are going to have a look at the a few of the more important ‘constructs’ and produce production grade queries for data extraction and for reports; enabling the reader to ‘hit the ground running’.

The Microsoft Business Intelligence Semantic Model (BISM)

Prior to any discussion pertaining to Data Analysis Expressions or DAX, it is imperative to understand that DAX is closely link to the Business Intelligence Semantic Model (hence forward referred to as the BISM). Microsoft describes the BISM as “a single model that serves all of the end-user experiences for Microsoft BI, including reporting analysis and dash boarding”.

In fact, in order to extract data from any tabular Analysis Services projects, the DAX language is the recommended language of choice. DAX itself is a rich library of expressions, functions that are used extensively within Microsoft Power Pivot and SQL Server Analysis Services tabular models. It is a ‘build out’ of Multi-Dimensional Expressions (MDX) and it does provide exciting new possibilities.

This said let us get started.

Installation of a Tabular Instance of SQL Server Analysis Services

With the advent of SQL Server and up, there are two flavors of Analysis Services:

  1. The Multidimensional Model
  2. The Tabular Model

Both of these models require separate instances. More often than not, for any project, I normally create a relational instance for run of the mill data processing and either a Tabular Instance or a Multidimensional Instance (depending upon the client/ project requirements). In short, one Analysis Services Instance cannot be both at the same time.

There is a way to change an instance to the opposite model (YES YOU CAN!!!) however it is NOT DOCUMENTED nor recommended by Microsoft. The main issue is that the server MUST BE stopped and restarted to move from one mode to the other.

To create a Tabular Instance simply create one with the SQL Server Media (BI edition or Enterprise edition or ). Install only the Analysis Services portion.

Getting started

SQL Shack industries has a relational database called DAXandTabularModel. A few months back they created a SQL Server Data Tools project to define and create their Tabular SalesCommissionReport Analysis Services database. The ins and outs of how this project was created is beyond the scope of this article however the project data may be seen below:

The project consist of four relational tables:

  1. Customer (containing customer details)
  2. dimDate (a Date entity with week number, month number, etc.)
  3. Invoice Header (the mommy a header has many details)
  4. Invoice Detail (the children)

This project model once deployed created the Analysis Services database with which we are now going to work.

Let us begin by bringing up SQL Server Management Studio and log into a Tabular Instance that I have created.

As a starting point we are going to have a quick look at how simple DAX queries are structured.

Having opened Analysis Services we note that there is a Tabular Analysis Services database called “SalesCommissionReport”. We shall be working with this database.

We now open a ‘New Query’ (see above).

Note that the appearance of the query window is somewhat reminiscent of query work surface for multidimensional queries.

For our first example let us take a look at a simple business query.

SQLShack Industries wishes to ascertain the total revenue, total cost and total margin for the varied invoiced items for a given time period.

In T-SQL we could express this in the following manner:

 

Select InvoiceNo,Datee as[Date],sum(Revenue)asRevenue,sum(Cost)asCost,

Sum(Margin)asMargin from DaxAndTheTabularModel

Group by InvoiceNo,Datee

 

Now!! Let us now see (in easy steps) how we may construct this same query within the world of DAX.

To begin, we wish to create a piece of code that will sum the revenue, cost and margin. Let us see how this is done!

We note the command DEFINE at the start of the query. The astute reader will note that the ‘InvoiceDetail’ entity (see above) contains the fields “Revenue”, ”Cost” and “Margin”. These are the fields that we shall be using. Note that the three measures are created below the word DEFINE (see above (in the screen dump) and below for a snippet of the code).

 

DEFINE

MEASURE InvoiceDetail[TotalRevenue]=SUM(InvoiceDetail[Revenue])

MEASURE InvoiceDetail[TotalCost]=SUM(InvoiceDetail[Cost])

MEASURE InvoiceDetail[TotalMARGIN]=SUM(InvoiceDetail[Margin])

 

A note is required at this point: The syntax for pulling fields from tabular databases is:
Entity[Attribute] as may be seen above. In our case “InvoiceDetail” is the entity and [TotalRevenue] the attribute.

Now that we have created our ‘DEFINED’ fields it is time to construct the main query.

We first add the word ‘EVALUATE’ below our declaration of the MEASURES. EVALUATE corresponds to the word SELECT in traditional SQL code.

Having issued the “EVALUATE” we now call for the fields that we wish to show within our query

Note that we utilize the ADDCOLUMNS function to achieve this. The columns within the query may be seen above and a snippet of code may be seen below:

 

ADDCOLUMNS(

ALL(InvoiceDetail[InvoiceNo],InvoiceDetail[Datee],InvoiceDetail[Margin]),

"Total Revenue",InvoiceDetail[TotalRevenue],

"Total Cost",InvoiceDetail[TotalCost],

"Total Margin",InvoiceDetail[TotalMARGIN]

)

 

To complete the code and knowing that I want the data ordered by the invoice number, I add an ORDER BY statement.

 

ORDER BY InvoiceDetail[InvoiceNo]

 

Thus our query is complete. Let us give it a spin!!

The results of the query may be seen below.

To recap

  1. DEFINE
  2. Create your define fields
  3. ADDCOLUMNS to your query including the defined fields
  4. Order by statement.

We shall see how to use this for constructive usage in a few minutes.

Another Example:

In the next example I want to show you how easily one may obtain data from two or more entities (tables).

After all in the real world, reporting queries normally do join to two or more tables. In our case we wish to look at ONLY data from the year

Once again we start with the necessary EVALUATE command. We remember from above that this is the equivalent to the SELECT statement.

This time however we are going to utilize the “CALCULATETABLE” function. “CALCULATETABLE” evaluates a table expression in a context modified by the given filters (see below in yellow)

EVALUATE CALCULATETABLE( SUMMARIZE( &#;InvoiceDetail&#;,&#;dimDate&#;[datee],&#;Customer&#;[CustomerName], "Sales",SUM( Invoicedetail[Revenue] ) ),InvoiceDetail[Yearr] = ) orderby&#;Customer&#;[CustomerName],&#;dimDate&#;[datee]

The astute reader will note that I am pulling the date from the ‘InvoiceDetail’ table (after all, an item is sold on a given date, not so? The customer name from the “Customer” table and the field “Sales” is sourced from the “InvoiceDetail” table.

When we run our query we find the following:

Normally SQLShack Industries work solely with an invoice date only as opposed to the date and time. Often the time is meaningless as it is etc. Invoice date and dollar values with thousandths and millionth of cents is also nonsense UNLESS one is in the banking industry. This said we shall “normalize” these values when we come to the report section of this article.

One of my FAVORITE queries

One of the most beautiful features of using DAX is the ability to ascertain values for “the same period last year”.

In this query we are going to once again SUMMARIZE ( sum() ) the values and add the columns. This one however is a tad tricky to understand thus I am going to put the query together in pieces.

Firstly, here are my DEFINE fields

 

DEFINE

MEASURE'Invoicedetail'[CalendarYear]=sumx('dimDate',Year('dimDate'[datee]))

MEASURE'InvoiceDetail'[PY Sales]=

CALCULATE(sumx('InvoiceDetail','Invoicedetail'[Revenue]),

SAMEPERIODLASTYEAR('dimDate'[Datee]))

The first line of code will give us the calendar year in which the items were sold. This helps the folks at SQLShack Industries set their frame of reference.

The next line of code is a bit convoluted however simply put “PY Sales” is defined as ‘InvoiceDetail’[Revenue] FOR THE SAME DATE AND MONTH LAST YEAR! This is where the function

1

SAMEPERIODLASTYEAR('dimDate'[Datee])

comes into play.

Let us finally proceed to add our query columns so that we can view the result set of the query.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

 

EVALUATE

ADDCOLUMNS(

FILTER<strong>(</strong>

SUMMARIZE<strong>(</strong>

'dimDate',

'dimDate'[datee],

'dimDate'[Month],

'dimDate'[Quarter],

'dimDate'[Weeknumber]

,"Sales: Today",sumx('InvoiceDetail','Invoicedetail'[Revenue]),

"Calender Year",'Invoicedetail'[CalendarYear]

),

//Filter the data

sumx('InvoiceDetail','Invoicedetail'[Revenue])&lt;&gt;0

),

// Add the past period DEFINED column

"Sales: Year ago Today",[PY Sales]

)

ORDER BY'dimDate'[Datee],'dimDate'[Month]

Once again, let us look at what each line of code achieves.

Under “SUMMARIZE”, from the dimDate entity (table), pull the date, month, quarter and week number of the invoice date.

THEN

1

"Sales: Today",sumx('InvoiceDetail','Invoicedetail'[Revenue]),

Sum the revenue for the date ( i.e. sum(revenue) group by date) for the CURRENT year under consideration.

We must remember that where current invoice year is then we create our “Last Year’s field” from the same date but for the year The important point being that aside from the lowest year’s data, EACH invoice date ‘has a turn’ to be a current date and the ‘same day and month’ BUT one year earlier (see the table below).

Prior YearCurrent Year
NULL

We also add the calendar year under consideration so that we know where we are in our table.

As a means of showing how to create a query predicate, I am including a filter within this code. It probably does NOT make any business sense and is meant purely to be demonstrative.

 

//Filter the data

sumx('InvoiceDetail','Invoicedetail'[Revenue])&lt;&gt;0

Finally, I add the revenue from the prior period. NOTE that this is OUTSIDE of the SUMMARIZE function and the reason for this, is to avoid applying the conditions set within the SUMMARIZE function to the prior year’s figures.

 

"Sales: Year ago Today",[PY Sales]

Finally we order our result set(by year, by month)

1

ORDER BY'dimDate'[Datee],'dimDate'[Month]

Having now created three queries and knowing that we could have created a plethora more, at this point in time we should now have a look at how these queries may be utilized in a practical reporting scenario.

Creating reports with DAX queries

We start (as we have in past discussions) by opening SQL Server Data Tools and by creating a new Reporting Services project. Should you be a bit uncertain how to create a Reporting Services project, please have a glance at one of my earlier articles where I do describe the project creation in detail OR contact me directly for some super “crib” notes!

We click OK to create the project. Once within the project, we right click on the “Report” folder and select “Add” and then “New Item” (see below).

The new item report screen is then brought into view (see below).

I select “Report” , give my report a name and the click “Add”.

We now find ourselves back at the report “drawing surface”.

Our first task is to create a “Shared Data Source” (as we have in past articles).

I right click on the “Shared Data Source” folder and select “Add New Data Source”

The “Shared Data Source” properties box is shown (see below).

We CHANGE the “Type” box to “Microsoft SQL Server Analysis Services” (see below).

We now click the “Edit” button and the “Change name, type and connection options” dialog box is displayed (see below).

I now set my server and choose our “SalesCommissionReport” database and test the connection (see below).

We click OK, OK and OK to exit and we are now ready to go!!

Once back on the drawing surface our first task is to create a data set to hold data PULLED from the Analysis Services tabular database. As I have described in earlier articles, a dataset may be likened to a bucket that is filled with water via a hose pipe coming from the faucet on the outside wall of your house. We then use the bucket of water to water the plants and in our case (with data) to populate our report(s).

AT THIS POINT I WOULD ASK THAT WE FOLLOW THE NEXT STEPS CAREFULLY as Microsoft has still to properly define a proper method to create TABULAR DATA sets. We MUST utilize a work-around.

We right click on the “Data Set” folder and select “Add Dataset”

The “Choose a data source and create a query” dialog box appears (see below).

I give my dataset a name (see above) and click “New” to create a new local data source (for this report only) see above.

The “New data source” box is brought up and I select our share data source that we defined a few seconds ago (see below).

I click OK to exit the “Data Source” dialog. We find ourselves back at the “Data Set” dialog box.

Now our queries are in fact text and the eagle-eyed reader will note that the “Query” dialog box is “greyed out”. Let the fun and games begin!!!

What is required to get Reporting Services to accept our DAX code is to select the “Query Designer” button (see above).

Clicking the “Query Designer” button brings up the “Query Designer” dialog box.

At this point we MUST SELECT “COMMAND TYPE DMX”. The button is found immediately above the “Command Type DMX” tool tip (see below).

We are then informed that “Switching from MDX to DMX will result in losing all current design content. Do you want to proceed?” We click “Yes”.

We now choose to go into design mode (see below). The button is found immediately above the “Design Mode” tool tip (see below).

We are now finally able to insert our query (see below).

Click OK to complete the process.

We are returned to our create data set screen. We now click “Refresh Fields” in the lower right hand portion of the screen dump above. We now select the “Fields” tab in the upper left have portion of the screen dump below.

The fields for our query may be seen above. Click OK to leave the “Data Set” dialog.

We find ourselves back on the drawing surface with our data set created.

Next, we drag a “Matrix” onto the drawing surface (see below).

We now remove the “Column Groups” by right clicking on the [Column Group] and select removing grouping only (see below).

We click OK to accept and leave the “Delete Group” dialog.

We now insert four more columns by right clicking on the top margin of the matrix (shown in black above).

Our finished surface may be seen above.

We now insert our first field (see above).

The screen shot above shows the matrix once all of the fields have been populated.

Looking at the column headings, we find them a bit cryptic. We shall change them as follows (see below).

Finally, we wish to change the ‘Hum Drum’ appearance by adding some fill to our text boxes. We choose “Khaki” for the headers and “Light Grey” for the values themselves.

..and

The “value” or result text boxes are filled in with Light Grey.

Let us give our report a whirl by selecting “Preview”

Here are the results. A mentioned earlier, we must now convert the date times to dates and have the dollar fields show only two decimal places.

We do so as follows:

Right clicking on the “datee” field “result box”, bring up the Textbox Properties dialog box (see below).

We select “Number”,”Date” and choose a format and click OK to finish. Our report now looks as follows.

Changing the “dollar” values, we now see the following:

And the final report…

Thus our report is complete and ready for the production environment!!!

Creating reports number two and three

I have taken the liberty of creating these two reports with the remaining two queries that we created above. The report creating process is the same as we have just seen for our Revenue Summary report.

And last but not least our “Same Period Last Year” report.

Thus we have completed our first venture into utilizing DAX expressions for queries which will be eventually utilized for reporting. Further they may be utilized with any data extracts utilizing Excel. But that is for another day.

Conclusions

With Power BI being the “Top of the pops” for reporting purposes within major industries and enterprises, it is and will become necessary for most SQL developers and BI specialists alike to become more fluent with the DAX language. More so for those enterprises that are heavily dependent on reporting via Excel.

Whilst DAX seems complex at first, it is fairly easy to learn and in doing so, you will put yourself ahead of the curve.

Happy programming!!!

Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years.

Steve has presented papers at 8 PASS Summits and one at PASS Europe and He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.

Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.

View all posts by Steve Simon

Latest posts by Steve Simon (see all)

General

About Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years. Steve has presented papers at 8 PASS Summits and one at PASS Europe and He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally. Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor. View all posts by Steve Simon

View all posts by Steve Simon →

6, Views 

© Quest Software Inc. ALL RIGHTS RESERVED.   |   GDPR   |   Terms of Use   |   Privacy

Sours: https://www.sqlshack.com/getting-started-with-data-analysis-expressions-sql-server/

Dax sql

DAX For SQL Folks: Part I- Intro to DAX, Power BI and Data Viz

 Introduction

In response to my approach in the popular MDX Guide for SQL Folksseries, I am going to try and repeat the same style of learning for the Data Analysis Expression(DAX) language. I am going to use SQL as a good frame of reference for starting or developing a new approach for improving your DAX learning experience. This is useful for developers starting to learn the DAX language to more advanced developers who have struggled to adjust to the language coming from a SQL background.

I am going to introduce DAX by first, drawing very strongly on the similarity and differences between DAX and SQL at the fundamental level. I am going to assume that readers learned SQL before DAX and that this fact could influence their DAX learning process. Ultimately, I will focus on how to use DAX with Power BI and more importantly tackle some core DAX and Power BI interactive reporting concepts along the way. The ultimate goal of the series is to help readers employ DAX in Power BI for effective analytical storytelling in their data visualization endeavors.

There are a great number of DAX resources available, but I especially draw heavily from those by Marco Russo and Alberto Ferrari. I recommend their books and websites for deep dives into some introductory topics I address here and those that I don't.

Part I - Intro to DAX, Power BI, and Data Viz

Dax is a broad topic, in Part I, I am going to address some key underlying concepts that will explain some unique DAX behaviors as we progress along in the series.  The sections will look at:

  • the importance of interactive Data Visualization today and the role DAX plays in Power BI to help a user tell analytical stories effectively.
  • the differences and similarities between the Relational Database and the Tabular Model, the databases that power T-SQL and DAX respectively.
  • understanding how interactive reports work and how user interactions and visuals on Power BI reports introduce implicit filters that affect the DAX calculations and expressions that you write.
  • understanding DAX as a functional Language compared to SQL as a declarative language. We compare the two languages using some basic queries.

Like most things, mastering DAX does not happen overnight. Coming from a SQL background, understanding these basic but essential topics should serve as a guide with SQL frame of reference when trying to understand some unique DAX behaviors or specific DAX function calls.

Power BI

Power BI is Microsoft's flagship Model-based business analytics service tool that provides interactive visualizations and business intelligence capabilities for firms and businesses of all sizes. With Power BI you can connect to hundreds of data sources and bring your data to life with live dashboards and reports. It has an interface that enables users to create their reports and dashboards easily or drill down through visuals to explore the data.

Power BI also integrates many other tools like R and Python that extend its visualization Capabilities. Ultimately whether generating ad-hoc reports, pursuing visualization in a Data Science project, or setting up an enterprise-level analytics solution, there are many visual and data modeling options available in the tool to help you achieve this.

DAX, Power BI, and Tabular Models

DAX is the primary language used to query Tabular Models in Power BI. In Power BI you can build a Tabular Model directly on top of your primary data sources or connect to a Tabular Model built as an Analysis Services Tabular Model Database  (SSAS Tabular Model Database).

In this series, we will use the second option. We will use portions of the Tabular Model from the AdventureWorksDW SSAS Tabular Model sample Database for all DAX queries and all Power BI formulas and expressions.  You can follow this part of the series without a copy of the AdventureWorksDW Tabular Model sample database. But to run or test DAX queries and Power BI formulas and expressions in the subsequent parts in the series you need to install a copy of this database.

For those new to DAX and Tabular Models, the easiest way to follow the example in the series is to download and install the AdventureWorksDW Tabular Model sample database. To do that you can follow the example in this article  DAX #2 – Installing AdventureWorks DW Tabular Model SQL Server to install a compatible version.

For those new to DAX and Tabular Models who want to learn how to create and deploy a SASS Tabular Model Database, this tutorial Adventure Works Internet Sales tutorial provides step-by-step lessons on how to do that.

Data Visualization

Data visualization is the presentation of data in a pictorial or graphical format. It involves techniques that enable developers to communicate effectively to decision-makers using visuals. Data visualization techniques can help one's audience grasp difficult concepts and identify new patterns when applied effectively. This enables the audience to act on the information.

Reporting has evolved, and with more recent studies showing decreasing average human attention span, data visualization today is not about showing dashboards and reports for your audience to interpret. Rather it involves showing them what is happening through new insights, identifying new patterns, and telling them what to do in response to the data. Reporting today is about making your point most convincing by selecting the appropriate visuals in a design that meets users' information needs and good experience. Your audience may admire your stunning reports, dashboards, and websites, but this does not mean they will come back if they did not find the information they need or if it was simply difficult to piece together what you are trying to communicate.

What most novices fail to realize is that there are an art and science to analytical storytelling and thus learning these techniques helps you turn data into information that can be used to drive better decision making. There are many books available for teaching these techniques. Apart from helping you choose the right visual for specific scenarios, they also teach you how to;

  • Craft the Big Idea, knowing your audience and setting the context
  • Think like a designer. Techniques usually reserved UX/UI experts
  • Reduce clutter (because less could be more) through the use of color sparingly and how to utilize space effectively and many more.

I suggest you grab one of these books to learn and practically apply the techniques they teach in your data Visualization efforts.

Interactive Data Visualization

Interactive visualization takes the concept of data visualization a step further. By using data modeling and other technologies, interactive visualization enables end-user to drill down into visuals for more detail, interactively changing what data they see and how it is processed. It allows users the freedom to fully explore and analyzed data.

The self-service capability of interactive visualization means that users can manipulate the data to find out specific things they need to know. Through this exploratory process, they can also be alerted to situations that require immediate attention. Also, because the data is often modeled on the business it improves collaboration; such that when everyone on a team is drilling through the same data, the team can identify and solve problems more easily.

Why is DAX important?

Power BI was designed to have self-service and easy-to-use drag and drop interface to enable various end-users to create reports and dashboards. As a result, It is possible to create some reports that show some valuable insights without using any DAX formulas at all. But when you want to create reports that showcase more advanced financial metrics, growth and trend calculations over time, then one needs DAX. Learning how to create effective DAX formulas and expressions will help you get the most out of your data.

In effect by combining some of the data visualization storytelling techniques mentioned above with some DAX expertise, pros can tell a story on one page that novices can't communicate in several pages. Some of the topics we discuss later. "Evaluation Context" could eventually help you write terse advanced DAX Formulas to improve your expertise. Note that like everything mastering DAX does not happen overnight.

How is DAX similar or different from SQL?

To be able but to answer this question one must first understand how a Relational Database is different from a Tabular Model. If you learned SQL before DAX, you should pay attention to the sections following because I believe this is what causes difficulty in transitioning from SQL to DAX.

The Relational Database Vs the Tabular Model

If you are reading this, you probably know that SQL is used to query tables in Relational Databases. Tables are the basic objects in Relational Databases.  DAX is also used to query tables in Tabular Models. Figure 1 below shows the parallels between the objects in a Relational Database and Tabular Model from the perspective of the two languages.

 

        Figure 1: Showing the basic parallels between a relational database and a Tabular model from the perspective of SQL and DAX Languages.

At this stage, your mind is thinking these are the same, in fact, if you open both the SQL Server AdventureWorksDW relational database and the SSAS AdventureWorksDW Tabular Model database in SSMS this is what you see. Similar setups, with a one-to-one mapping of tables in the  AdventureWorksDW Relational Database to tables AdventureWorksDW Tabular Model Database

 

Figure 2: Showing one-to-one mapping of tables in the AdventureWorksDW Relational Database to tables AdventureWorksDW tabular Model Database.

In this series, we are going to restrict our DAX queries to a few of the tables in the AdventureWorksDW Tabular Model with a one-to-one mapping of tables from the AdventureWorksDW SQL Server Database as shown in figure 3 below.

   Figure 3:  Showing a one-to-one mapping of how some seven tables in SQL Server AdventureWorks Database maps to Tabular Model tables

Figure 3 shows how seven tables in the AdventureWorksDW Relational Database are represented as tables in an SSAS AdventureWorksDW Tabular Model, with the exact names. Table and columns names remain the same by default when you build a Tabular model directly from a Relational Database.

Now let’s take a look at how tables are actually represented in a SQL Server Database vs how they are represented in a Tabular Models in the next section.

Introduction to Relationships in DAX

One major difference between a Tabular Model and a SQL Server Database is that tables exist as separate entities in SQL Server Database but in Tabular Model, tables are joined together to form part of a physical model as shown in Figure 4 below.

   Figure 4:  Shows how tables in the "AdventureWorksDW" relational database translate into tables in the Tabular Model 

In SQL Server you may develop a logical model diagram to define relationships between tables using primary and foreign keys as shown in figure 5 below, but note that this does not mean the tables are joined together when you create the physical SQL Server Database.

         Figure 5: Showing a SQL Server Database Logical data model diagram, not a physical data model.

On the other hand in a Tabular Model, you have to physically implement relationships during the model development phase.  Therefore when the Tabular Model is processed,  the engine joins all the tables together with a left join to form one physical Data model.

This means that in SQL Server if you want data from two tables, you have to explicitly declare how you want the tables to be joined (using join statements like inner join in your select statements). On the other hand, in DAX when querying two tables you don't have to explicitly join them in your query statement because all relationships are established during the model processing phase. You can see how this becomes significant between T-SQL and DAX later when we start writing some queries. With this key knowledge in hand let proceed to look at how this concept plays out in the DAX language as compared to T-SQL.

Introduction to Expanded Tables in DAX

In the previous section, we learned that in Tabular Models all tables are joined together with a left join from the many-to-one side of the relationships. For instance, from the model shown in figure 6 below, DimProduct is left joined to DimProductSubCategory, and DimProductSubCategory is also left joined to DimProductCategory.

 

Figure 6: Showing a physical model and actual joins represented in a physical Database.

The concept of Expanded Tables is the idea that, in DAX, every table in a model has a matching expanded version. The expanded version of a table contains all the columns of that table, plus all the columns of the tables that are on the one-side of a chain of many-to-one relationships starting from the source table. Using the three Product tables in the model in figure 6 for illustrations, the concept means that;

  • The expanded version of DimProductCategory only contains columns in that table. The reason being that even though it joined to another table, it is on the one-to-many side of the relationship.
  • The only table with a relationship to DimProductCategory is DimProductSubCategory.DimProductSubcategory is on the one-to-many side of the relationship, therefore, the expanded version of DimProductSubcategory includes the columns in itself (Native columns) plus all the columns (Related columns) in the DimProductCategory table.
  • Similarly, the expanded version of DimProduct includes the columns in itself (Native columns) plus all the columns (Related columns) in the DimProductCategory and DimProductCategory tables.

Note that table expansion goes from the many-to-one side of a relationship but not the other way around, also table expansion does not stop at the first or second level, it continues as long a many-to-one side relationship can be further established.

For example, from the model, DimFactResellerSales can reach DimProductCategory following only many-to-one relationships. Thus, the expanded version of DimFactResellerSales contains all the columns in DimProduct, DimProductSubcategory, and DimProductCategory tables. Moreover, DimFactResellerSales is on the many-to-one side of the relationship with DimDate, DimReseller, and DimSalesteritoy, therefore, the expanded version of DimFactResellerSales contains DimDate, DimReseller, and DimSalesteritoy too. In other words, the expanded version of DimFactResellerSales contains the entire data model.

For those conversant with multi-dimensional modeling, when modeled properly, essentially the extended versions of the fact tables will contain most of the columns in the entire tabular model.

Expanded tables, filters, and Power BI Reports

The concept of Expanded Tables as explained above means that in DAX, a column can belong to multiple tables at the same time. The concept is useful because it provides a clear explanation of how filtering on a column in one expanded table propagates through the model and thus can affect the results of DAX formula or DAX expression written to target a different Expanded Table containing the same column. Once a filter is being applied to a column, all the Expanded Tables containing that column are filtered.

There are a few points to note about the concept:

  1. The concept of Expanded Tables, the expansion process utilizes relationships defined in the Model. Therefore, once a table has been expanded, the relationships have been included in the expanded version of the Tables. This should help explain why for instance you would not explicitly declare joins in DAX like in SQL.
  2. When DAX formulas, expressions, visuals, and user-actions on a Power BI report references a column in a table, they are referencing the expanded version of that table.
  3. When you apply filters on column either through formulas, expressions, user-action, or visuals, they do not work in isolation. When used in a Power report they are applied to the model at the same time. When DAX is evaluating your formula or expression, it must do it in the context of other filters applied to the model at that current time as a result of user-action, visuals, and even other DAX formulas.
  4. In short, visuals, user-action and even other formulas on a report can introduce external filters to your DAX formulas leading to unexpected results if these concepts are not well understood.

The concept and its implications might seem a bit complicated at this stage of the series, chew on it a bit, we will return to them later in the various parts of the series.

The DAX Language vs the T-SQL Language

We have seen the similarity and differences between the Relational Database and a Tabular Model. Now let's look at the major similarity and differences between the DAX languages compared to the T-SQL language.  We will do this by initially illustrating syntax differences between DAX and T-SQL using very simple and basic queries.

For instance, Listing 1 below shows how to select data from the DimProduct Table in both databases with SQL and DAX.

Listing 1:

Results;

 

As we can see from the figures above, both SQL and DAX return similar results.

From SQL background if you hear querying, your first inclination is something resembling

SELECT Blah Blah…

so what is going on with DAX here? Well, unfortunately, SQL is a declarative language while DAX is a purely functional language. With SQL, you define what you need by declaring the set of data you want to retrieve using SELECT and JOIN statements, without worrying about how the engine retrieves the information. DAX, on the other hand, is a functional language, i.e., in DAX, every expression is a function call. Function parameters can, in turn, be other function calls as below.

Function2(Function1())

With this knowledge let's get a feel of DAX queries and this functional stuff.

Introduction to a DAX Query

In Listing 1 above we encountered the EVALUATE function. First of all in DAX, if you need to query the data in a tabular model, you need the EVALUATE function. The EVALUATE function is used to return the result set as a table. It is the equivalent to using SELECT FROM statement in T-SQL to return columns and rows from a table. The EVALUATE function has a required syntax of

EVALUATE('table name').

In Listing 1 it was not used with any column projection function so the effect is similar to

SELECT * FROM

We will explore the complete syntax of EVALUATE and differences between DAX and SQL queries in our next series "Querying with DAX", but for now let's explore the functional nature of DAX further.

DAX as a Functional language

Functional language essentially means the language is written as functions nested together. In Listing 1 there was only one function, EVALUATE, in the DAX query. For instance, let's say now we want to select Top 3 records from the DimProduct table. Listing 2 below shows the queries to achieve these results.

Listing 2:

 

Results;

 

As we can see from the results above, they both return 3 records. Also, we can see that the DAX expression chained two functions (EVALUATE, TOPN ) together as shown below. Here TOPN nested within EVALUATE.

EVALUATE(TOPN())
  • Logically, it is easy to understand that the inner function is processed first and passed on to the next in the chain.
  • Required Parameters for most DAX functions are Columns, Tables, and expressions

Note that in SQL we also use some functions, but statements are not all functions.

Now, let's expand the previous query by filtering the DimProduct table to black products only. Listing 3 below shows the queries to achieve this result.

Listing 3:

SQL:

SELECT TOP(3) *  FROM DimProduct WHERE DimProduct.Color = 'Black'

DAX:

EVALUATE(TOPN(3,(FILTER(DimProduct, DimProduct[Color] = "Black" ))))

Yet again we see that DAX just chained three functions together as below.

EVALUATE(TOPN((FILTER())))

In SQL you can combine functions, clauses, and statements when programming. The good thing with DAX is that its functional form does not change; the code is all functions. Therefore when you learn it as a query language—its original use—you will know everything needed to also use it as a programming language.

For those new to functional languages, syntax formatting may make the DAX appear unlike functional in the beginning to you. For instance, the query in Listing 4 below is the same as the DAX query in Listing 3 above only formatted differently.

Listing 4:

DAX:
EVALUATE ( TOPN ( 3, ( FILTER ( DimProduct, DimProduct[Color] = "Black" ) ) ) )

One thing to bear in mind is that the evaluation of parameters in nested functions in DAX could result in complex query plans that the DAX engine executes to compute the results. Therefore, selecting the right functions to use and the order in which the functions are nested have a strong impact on both the result and the performance of the engine. Although the way one writes SQL also affects the query engine, the SQL query optimizer does a better job in finding the optimal query plan. In other words, in DAX the programmer bears more responsibility in writing good code. We will learn the different ways various DAX constructs can return the same results later in the series.

Summary

First, we learned of the importance of the use of data visualization techniques. We learned that applying these techniques can increase one's potential to effectively communicate decision making insights to drive action. There are many books available that teach this art and science of analytical storytelling.

We also learned the benefits of interactive visualization that comes with tools like Power BI. Interactive visualization tools take the concept of data visualization a step further by using data modeling and technology to enable end-user to drill down visuals to fully explore and analyze data.

We examined the key similarities and differences between and a SQL Server Database and a Tabular Model that powers T-SQL and DAX, respectively. We further learned that:

  • In both databases the basic elements are tables. In SQL Server Database tables are isolated units whilst in the Tabular Model tables are all joined into one physical data model.
  • Because tables are left joined to other tablets on the many-to-one side, tables in Tabular Model are essentially extended tables.
  • Understanding the extended tables concept and other principles will help one to grasp the effects of filter propagation, interactivity and their effects on DAX formulas and expression that we write.

Finally, we learned that even though both DAX and SQL languages can achieve the same query results, SQL is a declarative language whilst DAX is a purely functional language. Functional languages essentially chain functions together by nesting one function within another.

Some of the Tabular Model concepts discussed here that make interactive visualization possible are also the ones that introduce the complexities in how DAX formulas and expressions are evaluated by the DAX engine. For instance, in the concept of extended tables, the fact that columns can belong to multiple tables means that filters introduced by user-actions and visuals on a report become "external" filters that affect your DAX formulas and expressions. We will explore these effects later in the series, but this means that the concept of extended tables must be well understood.

The topics we explored in this part are to serve as a reference when transitioning from SQL to DAX. One should return to them as often as possible to reset their frame of reference.

Next in the Series

In the next installment of the series, we are going to look at Querying with DAX, which will explore DAX queries exclusively. We will do this by comparing DAX queries with their SQL examples. For instance, we will look at how all SQL clauses in the query execution context below translated functionally into DAX and more.

SELECT <select_list> FROM <left table> join<right_table> WHERE <where_predicate> GROUP BY <group_by_specification> HAVING <having_predicate> ORDER BY <order_by_list>;

We will also explore more on some of the concepts introduced here as we go along.

 

 

Sours: https://www.sqlservercentral.com/articles/dax-for-sql-folks-part-i-intro-to-dax-power-bi-and-data-viz
DAX for SQL Folks - DAX as a Query Language

Obviously T-SQL and DAX are two different query languages and it serves entirely different purposes.However, as a person who came from database development background , when I learn DAX initially, I always started thinking SQL way and tried to achieve same thing from DAX. The reason for that thinking process is, it is always easy to learn something new when we compare same thing with something we already know. So in this blog post, I try to cover very basic DAX statements with comparison with T-SQL. Again, the idea is start with T-SQL and try to obtain same result set or the functionality from DAX.

For this post, I use Application.Cities, Application.StateProvinces tables in WideWorldImporters database, and same tables are imported in to tabular model.Check the database diagram bellow. As you can see, there is one to many relationship between Cities and StateProvinces tables.

image

This is how same tables and relationship looks in Tabular model.

image

Lets start with simple select statement.

1. Select all the records from Cities table

SQLDAX
SELECT * FROM Application.Cities EVALUATE Cities
imageimage

2. Select few columns from the City table

SQLDAX

SELECT CityName,
       LatestRecordedPopulation
FROM Application.Cities;

EVALUATE
ALL(Cities[CityName],Cities[LatestRecordedPopulation])

imageimage

3. Select few columns and order result set by LatestRecordedPopulation descending order.

SQLDAX

SELECT CityName,
        LatestRecordedPopulation
FROM Application.Cities
ORDER BY LatestRecordedPopulation DESC;

EVALUATE
ALL(Cities[CityName],Cities[LatestRecordedPopulation])
ORDER BY Cities[LatestRecordedPopulation] DESC

imageimage

4. Filter out cities where LatestRecordedPopulation>

SQLDAX

SELECT CityName,
        LatestRecordedPopulation
FROM Application.Cities
WHERE LatestRecordedPopulation>;

EVALUATE
     FILTER(
         ALL(Cities[CityName],Cities[LatestRecordedPopulation]),
         Cities[LatestRecordedPopulation]>
         )

imageimage

5. Group cities by City  name and apply  sum aggregation to LatestRecordedPopulation field.

SQLDAX

SELECT CityName,
        SUM(LatestRecordedPopulation) AS LatestRecordedPopulation
FROM Application.Cities
GROUP BY CityName

EVALUATE
     SUMMARIZE
    (
     Cities,
     Cities[CityName],
     &#;LatestRecordedPopulation&#;,
     SUM(Cities[LatestRecordedPopulation])
     )

imageimage

Whole propose of the blog post is not to teach you DAX, but to help you write very simple select statement in DAX if you are new to DAX and have not written any DAX queries before. Additionally,  when it comes to DAX, same query can be written in multiple ways and therefore note that DAX queries written in this post are not the only way to achieve same result set. In next post I will cover few other SQL statement in DAX. Cheers !!!

Like this:

LikeLoading

Related

Sours: https://asankap.wordpress.com//03/27/how-to-write-your-sql-query-in-dax/

Now discussing:

From SQL to DAX: Filtering Data

Consider the following SQL syntax:

SELECT * FROM DimProduct WHERE Color = 'Red'

It corresponds to this DAX query using FILTER:

EVALUATE FILTER ( Product, Product[Color] = "Red" )

You can also use a DAX query using CALCULATETABLE:

EVALUATE CALCULATETABLE ( Product, Product[Color] = "Red" )

In case of a simple SQL query like the initial query, there are no semantic differences between the two corresponding DAX options. However, we see a different behavior when other expressions or a more complex filter condition are present.

Consider a double filter like this one:

SELECT * FROM DimProduct WHERE Color = 'Red' AND ListPrice >

You have two options using FILTER. The simplest one is applying the same logical expression to a single FILTER:

EVALUATE FILTER ( Product, AND ( Product[Color] = "Red", Product[ListPrice] > ) )

With this approach, the logical conditions (color red and list price greater than ) are applied to every row in Product, even if some optimization might happen internally to reduce the number of comparisons made.

This is the corresponding solution using CALCULATETABLE:

EVALUATE CALCULATETABLE ( Product, Product[Color] = "Red", Product[List Price] > )

The filter arguments in CALCULATETABLE are always put in a logical AND condition. Even if the results produced by FILTER and CALCULATETABLE are identical, the performance might be different. The CALCULATETABLE could be faster if the number of values returned by each logical condition on single columns is relatively small (more details in the Understanding DAX Query Plans white paper). In fact, every filter argument in CALCULATETABLE corresponds to an internal FILTER on a single column. The previous CALCULATETABLE syntax is internally rewritten as:

EVALUATE CALCULATETABLE ( Product, FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" ), FILTER ( ALL ( Product[List Price] ), Product[List Price] > ) )

Do not assume that CALCULATETABLE is good and FILTER is bad. Every CALCULATETABLE internally incorporates one or several FILTER, and performance is generally determined by the granularity of each FILTER (even if the DAX engine might perform further optimization).

In the case of an OR condition, you should write the entire logical condition as a single condition even in CALCULATETABLE, including a FILTER condition inside. Consider the following SQL statement:

SELECT * FROM DimProduct WHERE Color = 'Red' OR Weight >

The corresponding DAX syntax using FILTER is:

EVALUATE FILTER ( Product, OR ( Product[Color] = "Red", Product[Weight] > ) )

The corresponding CALCULATETABLE version requires an explicit FILTER argument in order to specify a logical condition that includes two columns of the same table (in this case, no automatic FILTER rewriting is possible).

EVALUATE CALCULATETABLE ( Product, FILTER ( Product, OR ( Product[Color] = "Red", Product[Weight] > ) ) )

At this point, you might think that FILTER is simpler to use. However, you should always consider the CALCULATETABLE option, because of the different behavior when you have nested calculations.

For example, consider the following SQL query that returns red products with a total sales amount greater than , and an average sales amount greater than 3, in the calendar year

SELECT * FROM DimProduct p WHERE Color = 'Red' AND ( SELECT SUM([SalesAmount]) FROM [FactInternetSales] s INNER JOIN DimDate d ON s.OrderDateKey = d.DateKey WHERE s.ProductKey = p.ProductKey AND d.CalendarYear = ) > AND ( SELECT AVG([SalesAmount]) FROM [FactInternetSales] s INNER JOIN DimDate d ON s.OrderDateKey = d.DateKey WHERE s.ProductKey = p.ProductKey AND d.CalendarYear = ) >

The equivalent DAX expression using only FILTER is shorter than the SQL expression. However, you would still specify the Calendar Year filter in two calculations (sum and average):

EVALUATE FILTER ( FILTER ( Product, Product[Color] = "Red" ), AND ( CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), 'Date'[Calendar Year] = ) > , CALCULATE ( AVERAGE ( 'Internet Sales'[Sales Amount] ), 'Date'[Calendar Year] = ) > ) )

Using CALCULATETABLE, the filter arguments (color and calendar year) are applied to the entire expression specified in the first argument. For this reason, the two CALCULATE expressions in the FILTER of the following DAX query do not have to include the filter on calendar year, because it is “inherited” from the outer CALCULATETABLE filters.

EVALUATE CALCULATETABLE ( FILTER ( Product, AND ( CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) ) > , CALCULATE ( AVERAGE ( 'Internet Sales'[Sales Amount] ) ) > ) ), Product[Color] = "Red", 'Date'[Calendar Year] = )

Using CALCULATETABLE you propagate a filter to all the expressions embedded in the first argument. This results in shorter syntax simplifying the query, even if it requires particular attention – outer CALCULATETABLE and CALCULATE statements manipulate the filter context and affect any inner expression.

FILTER

Returns a table that has been filtered.

CALCULATETABLE

Context transition

Evaluates a table expression in a context modified by filters.

AND

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

OR

Returns TRUE if any of the arguments are TRUE, and returns FALSE if all arguments are FALSE.

CALCULATE

Context transition

Evaluates an expression in a context modified by filters.

Articles in the From SQL to DAX series

Sours: https://www.sqlbi.com/articles/from-sql-to-dax-filtering-data/


511 512 513 514 515