Testing DAX table functions
We can test the speed of measures using the performance analyzer in Power BI to see which table function used in a formula performed best. Below are the results of 10 tests per measure. SUMMARIZECOLUMNS() and SELECTCOLUMNS() are neck and neck, with the old SUMMARIZE() function finishing last.
Typically, when working with a large semantic model, you will not be able to see the actual data you are working with, which requires you to visualize the tables in your mind when creating virtual tables in DAX measures.
It is good to learn the different structures of creating DAX measures, which you can assign to variables. Remember, a measure is always scalar and never a table, so it returns a value within whatever filter context it is in, e.g., months in a matrix, the measure being sales.
But you can still create a virtual table in a DAX measure, but the return result needs to be a scalar; this is typically done with formulas, such as COUNTROWS(), SUMX, AVERAGEX(), MINX(), MAX(), and COUNTX().
We can use the FactInternetSales table and the DimDate table from the AdventureWorks Database.
FactInternetSales is joined to DimDate using the OrderDate.
Let’s say we wanted to get the number of customers with only one order and the average number of orders per customer. We can create the following measures:
-- First Measure Count of unique Customers
Customers = DISTINCTCOUNT('FactInternetSales'[CustomerKey])
--Second Measure Customers with one order
CustomersWithOneOrder =
VAR _CustomerOrders = SUMMARIZE('FactInternetSales', -- Table
'FactInternetSales'[CustomerKey], -- Groupby
"Orders", DISTINCTCOUNT('FactInternetSales'[SalesOrderNumber])
)
VAR _OneOrder = FILTER(_CustomerOrders, [Orders] = 1)
RETURN
COUNTROWS(_OneOrder)
-- Third measure % of customers that only have one order
% One Order = DIVIDE([CustomersWithOneOrder], [Customers],0)
--Average Orders per Customer
Orders Per Customer =
VAR _Customers = DISTINCTCOUNT(FactInternetSales[CustomerKey])
VAR _Orders = DISTINCTCOUNT(FactInternetSales[SalesOrderNumber])
RETURN
DIVIDE(_Orders, _Customers, 0)We can put the measure into a matrix with dates, and now we have a useful metric that we can use as a KPI for the business.
For the average orders per customer table, we create a virtual table in our measure to get the results we need, but there are different methods of creating virtual tables in measures, so it’s good to test them all.
Method 1: Summarize()
CustomersWithOneOrder =
VAR _CustomerOrders = SUMMARIZE('FactInternetSales', -- Table
'FactInternetSales'[CustomerKey], -- Groupby
"Orders", DISTINCTCOUNT('FactInternetSales'[SalesOrderNumber]) -- unique count
)
VAR _OneOrder = FILTER(_CustomerOrders, [Orders] = 1)
RETURN
COUNTROWS(_OneOrder)Method 2: SELECTCOLUMNS()
Note that, as the SELECTCOLUMNS() function iterates row by row, we have to use CALCULATE to overcome this
CustomersWithOneOrder2 =
VAR _CustomerOrders = SELECTCOLUMNS(VALUES('FactInternetSales'[Customerkey]), --Unique column group by
"Customerkey", [CustomerKey], --
"Orders", CALCULATE(DISTINCTCOUNT('FactInternetSales'[SalesOrderNumber])) -- unique order count
)
VAR _OneOrder = FILTER(_CustomerOrders, [Orders] = 1)
RETURN
COUNTROWS(_OneOrder)METHOD 3: SUMMARIZECOLUMNS()
The measure using summarize columns is the same as SUMMARIZE()
CustomersWithOneOrder3 =
VAR _CustomerOrders = SUMMARIZECOLUMNS( 'FactInternetSales'[Customerkey], -- group by
"Orders", CALCULATE(DISTINCTCOUNT('FactInternetSales'[SalesOrderNumber]))
)
VAR _OneOrder = FILTER(_CustomerOrders, [Orders] = 1)
RETURN
COUNTROWS(_OneOrder)
METHOD 4: FILTER()
This is the simplest way and perhaps the fastest, but we should test it.
CustomersWithOneOrder4 =
COUNTROWS(
FILTER(
VALUES('FactInternetSales'[CustomerKey]),
CALCULATE(DISTINCTCOUNT('FactInternetSales'[SalesOrderNumber])) = 1
)
)
The winners are at the top of the page! Scroll back up!