Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 123 of 212

111 C H A P T E R 6 | More analytics Function type Function Description PERCENTILEX.INC() Returns the kth percentile of an expression evaluated for each row in a column, where k is in the range 0 to 1, inclusive. Text CONCATENATEX() Concatenates the results of an expression evaluated for each row in a table. Other GROUPBY() Returns a table with selected columns with the results of evaluating an expression for each seat of GroupBy values. INTERSECT() Returns a table containing values in one table that are also in a second table. ISEMPTY() Returns a Boolean value indicating whether a table is empty. NATURALINNERJOIN() Returns a table after performing an inner join of two tables. NATURALLEFTOUTERJOIN() Returns a table after performing a left outer join of two tables. SUMMARIZECOLUMNS() Returns a table containing combinations of values from two tables for which the combination is nonblank. UNION() Returns a table containing all rows from two tables. Using variables in DAX You can now include variables in a DAX expression to break up a complex expression into a series of easier-to-read expressions. Another benefit of using variables is the reusability of logic within the same expression, which might possibly improve query performance. Here's an example that focuses on sales of products in categories other than bikes and finds the ratio of the sales of these products with a unit price less than $50 to all sales of these products. First, to create this measure without variables and without using intermediate measures, you would use the expression shown in Example 6-1. Example 6-1: Creating a complex DAX expression without variables Non Bikes Sales Under $50 % of Total:= sumx( filter(values(Category[CategoryName]), Category[CategoryName]<> "Bikes"), calculate(sum([SalesAmount]),'Internet Sales'[UnitPrice]<50) ) / sumx( filter(values(Category[CategoryName]), Category[CategoryName]<> "Bikes"), calculate(sum([SalesAmount])) ) To reproduce the same results by using an expression with variables, you can use the expression shown in Example 6-2. You can use as many variables as you like in the expression. Use the VAR keyword to introduce each variable, then use the RETURN keyword for the final expression to resolve

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016