Chance Insight – Analytics Blog (AI Enhanced)

Category: DAX

  • DAX Optimization – Analyzing the Query plan and storage retrieveal

    A summary of the steps for executing a Power BI report measure loaded in a report to return the data is in the table below. Power BI and tools like Tabular Editor and DAX Studio communicate with the tabular model in the Power BI service via the XMLA endpoints. DAX expressions for creating measures can be created in PBI Desktop, PBI Service, and Tabular Editor. A DAX query can be created and edited in the DAX Query tab of the PBI client and DAX Studio. The human-readable internal query plan and xmsQL can be viewed in DAX Studio. Direct queries SQL can be created in the PBI desktop client or connected directly to tables or views in an SQL database. SQL Server profile can be used to trace commands sent to the tabular model and every XMLA query the engine generates behind the scenes.

    Using DAX Studio, we can analyze the query plans made by the Formula Engine (FE) to look for issues that may slow the query. Using the server timings tab, we can look at the xmSQL steps to understand where the time is spent by the storage engine (SE) in data retrieval.


    DAX Data Retrieval Steps (DAX measure)

    StepProcessorCode Description
    1. Report visual with DAX Measure loads the DAX Expression Power BI Report DAX Expression (Measure)A visual containing a DAX measure triggers a data request when it loads or refreshes. The measures DAX expression is stored in the model metadata.
    2. The DAX expression is converted to a DAX Query Power BI Client / Query GeneratorDAX QueryDAX Query
    3. DAX Query sent via XMLA to Tabular engineXMLA Protocol LayerXMLA CommandThe client sends the DAX query inside XMLA statement to the Analysis Services (Tabular) Engine.
    4. Formula engine parses and builds the query planFormula Engine (in Tabular engine)Internal query pan (can view in DAX studio, but code is C++ internal)The FE parses, validates, and creates an internal query plan (DAX query execution plan)
    5. The Formula engine requests data retrieval from the storage engineStorage engine (Vertipaq Storage Engine or Direct Query Storage Engine (e.g., SQL Server)SE executes compressed scans, filter, and group operations, and returns the results to the FEFE delegates data scanning and aggregations to the storage engine (import, DirectQuery or composite)
    6. The storage engine executes and returns results to FEVertipaq Database or external
    SQL database, e.g., SQL Server    		xmSQL (Vertipq) / SQL query (DirectQuery)SE executes columnar scans, filter, and group operations, and returns the results to the FE
    7. Formula Engine performs any final calculationsFormula Engine (FE) Internal opeations (C++)The FE completes all non-foldable DAX logic (e.g., iterators, context transactions, SUMX, FILTER) using results returned from the SE.
    8. Final result returned and rendered in the visualPower BI ClientTabular result (via XMLA reponse)Tabular result (via XMLA response)

    Test 1: A Simple DAX measure

    DAX Expression (Created in Power BI Desktop)
    Fare Amount = SUM(tlc_green_trips_2016[fare_amount])

    DAX Query (DAX Studio)

    DEFINE 
    —- MODEL MEASURES BEGIN —-
    MEASURE _Measures[Fare Amount] = SUM(tlc_green_trips_2016[fare_amount])
    —- MODEL MEASURES END —

    DAX Query that returns Result (one row, one column)

    EVALUATE
    { [Fare Amount] }

    DAX Query that returns a result for a table /visual

    EVALUATE
    SUMMARIZECOLUMNS(
    DimZone[Zone_id],
    “Fare”, [Fare Amount] 
    )

    Query Plan (DAX Studio)
    The query plan is created by the Formula engine. There are 2 plans:

    1. Logical Query Plan – What needs to be computed. Conceptual level – FE only – DAX operations: Calculate, Filter, Sum

    2. Physical Query Plan – How to compute (created by FE and SE cooperation) – Internal operations, e.g,. Scan_vertipaq, SPool, GroupBy, CrossApply)

    Power BI query plan

    Things to look out for in the query plan are as follows:
    1. Iterator nodes (e.g., FilterIterator, SumXIterator) that are slow, row-by-row calculations (FE bound).
    2. CallbackDataID: Commands by SE back to FE (which can cause a major slowdown.
    3. NonEmpty or CrossJoin nodes on large tables: Cartesian joins are dangerous and can slow large models.

    Good things are:
    1. Scan_Vertipaq and Sum_Vertipaq show aggregation done in SE
    2. GroupBy and AggregationSpool show grouping done by SE
    3. TREATAS and CALCULATETABLE – relationship filtering applied efficiently.

    So we see in the above example the use of Scan_Vertipaq and Sum_Vertipaq, CALCULATETABLE, and TREATAS
    And we can’t see any of the bad points that could slow down the query.


    xmSQL (DAX Studio (Server Timings)
    The storage engine activity is logged in the server timings tab of DAX Studio. The request is broken down into 2 steps plus the metrics:

    Power BI xmSQL server timings

    The steps are as follows:

    1. Scan –

    SET DC_KIND=”AUTO”; — how to set the data cache
    SELECT
    ‘DimZone'[Zone_id] —Selects the columns to group by for the next step to use as keys.
    FROM ‘DimZone’;

    Estimated size: rows = 263 bytes = 2,104

    2. Then the next step does the work. It uses the results from the first scan in the WHERE clause.

    Note the N in N’60’ is NVARCHAR.
    WHERE (NOT (([a0] IS NULL))) — allows for entries not in the zoneid list in the first scan.
    The SELECT is limited to return 1M rows as default safety in xmSQL..

    Note: Grouping and SUM are done in the SE, not the FE – so this is good as SE is faster than the FE (which is single threaded)
    The relationship (Zone_ID) is correctly folded as seen in the IN(…)

    SELECT TOP (1000001) *
    FROM (
    SELECT [t0].[pickup_location_id],
    SUM([t0].[fare_amount]) AS [a0]
    FROM [tlc_green_trips_2016] AS [t0]
    WHERE (
    (
    [t0].[pickup_location_id] IN (
    N’60’,
    N’27’,
    N’202′,
    N’185′,
    N’101′,
    N’258′,
    …….
    N’236′,
    N’44’
    )
    )
    OR ([t0].[pickup_location_id] IS NULL)
    )
    GROUP BY [t0].[pickup_location_id]
    ) AS [MainTable]
    WHERE (NOT (([a0] IS NULL)))

    So to summarize, the Vertipaq query looks good. The heavy work of filtering and grouping is done by the storage engine, so the query is folding correctly and being passed back to the FE to do the work..

  • Multiple DAX Measures -Creation methods

    There are different ways we can build multiple measures at once in Power BI, and we can ask AI to build us the scripts to do the job for us. First,I will tell AI what tables I have from the publicly available Adventure Works database and then ask for generate some useful measures we can use.

    The methods we use are as follows:
    1. DAX query (as part of Power BI desktop).
    2. Tabular Editor (third-party tool)
    3. TMDL


    DAX query multiple measure creation.
    The first method is to create a script in DAX query:

    The tables that I am using are below:

    AI gives us the code we need to create 10 measures for the Reseller table.
    It creates a nice DAX query that we can use to add measures.

    -- Define 10 foundational measures for AdventureWorksDW Reseller model
DEFINE
    MEASURE FactResellerSales[Sales] =
        SUM ( FactResellerSales[SalesAmount] )

    MEASURE FactResellerSales[Total Cost] =
        SUM ( FactResellerSales[TotalProductCost] )

    MEASURE FactResellerSales[Gross Profit] =
        [Sales] - [Total Cost]

    MEASURE FactResellerSales[Gross Margin %] =
        DIVIDE ( [Gross Profit], [Sales] )

    MEASURE FactResellerSales[Order Count] =
        DISTINCTCOUNT ( FactResellerSales[SalesOrderNumber] )

    MEASURE FactResellerSales[Units Sold] =
        SUM ( FactResellerSales[OrderQuantity] )

    MEASURE FactResellerSales[Avg Order Value] =
        DIVIDE ( [Sales], [Order Count] )

    MEASURE FactResellerSales[Sales YTD] =
        TOTALYTD ( [Sales], 'DimDate'[FullDateAlternateKey] )

    MEASURE FactResellerSales[Sales PY] =
        CALCULATE ( [Sales], DATEADD ( 'DimDate'[FullDateAlternateKey], -1, YEAR ) )

    MEASURE FactResellerSales[Sales YoY %] =
        DIVIDE ( [Sales] - [Sales PY], [Sales PY] )

-- Example result set to validate measures (optional)
EVALUATE
SUMMARIZECOLUMNS(
    'DimDate'[CalendarYear],
    'DimProductCategory'[EnglishProductCategoryName],
    'DimProductSubcategory'[EnglishProductSubcategoryName],
    "Sales", [Sales],
    "Gross Profit", [Gross Profit],
    "GM %", [Gross Margin %],
    "Orders", [Order Count],
    "Units", [Units Sold],
    "AOV", [Avg Order Value],
    "Sales YTD", [Sales YTD],
    "Sales PY", [Sales PY],
    "YoY %", [Sales YoY %]
)
ORDER BY 'DimDate'[CalendarYear], 'DimProductCategory'[EnglishProductCategoryName], 'DimProductSubcategory'[EnglishProductSubcategoryName]

    I can then add them to my model by clicking on each measure: ‘Update model: Add measure’. So it does take a little effort.


    My measures are created, but disorganised, so I’d like them in a folder without us having to do the work. This is where the tabular editor comes in.

    Tabular editor C# Script
    We can use AI to give us a C# script that we can run in the Tabular editor (you need to enable unsupported features in File > Preferences), for it to run, but it does run. Note I’m using the free tabular editor 2 here.

    // AdventureWorksDW 2022 - Starter measures (create or update)
// Target table: FactResellerSales
// Folders: Measures\Sales Performance, \Customer Insights, \Product Performance, \Time Intelligence

var t = Model.Tables["FactResellerSales"];
if (t == null) throw new Exception("Table 'FactResellerSales' not found.");

// create-or-update helper
int created = 0, updated = 0;
System.Action<string,string,string,string> Make = (name, expr, folder, fmt) =>
{
    Measure m = null;
    foreach (var mm in t.Measures) { if (mm.Name == name) { m = mm; break; } }
    if (m == null) { m = t.AddMeasure(name, expr); created++; } else { m.Expression = expr; updated++; }
    if (!string.IsNullOrWhiteSpace(folder)) m.DisplayFolder = folder;
    if (!string.IsNullOrWhiteSpace(fmt))    m.FormatString  = fmt;
};

// folder constants
var F_Sales = "Measures\\Sales Performance";
var F_Cust  = "Measures\\Customer Insights";
var F_Prod  = "Measures\\Product Performance";
var F_Time  = "Measures\\Time Intelligence";

// SALES PERFORMANCE
Make("Sales",               "SUM(FactResellerSales[SalesAmount])",                F_Sales, "#,0");
Make("Total Cost",          "SUM(FactResellerSales[TotalProductCost])",           F_Sales, "#,0");
Make("Gross Profit",        "[Sales] - [Total Cost]",                             F_Sales, "#,0");
Make("Gross Margin %",      "DIVIDE([Gross Profit],[Sales])",                     F_Sales, "0.00%");
Make("Order Count",         "DISTINCTCOUNT(FactResellerSales[SalesOrderNumber])", F_Sales, "#,0");
Make("Units Sold",          "SUM(FactResellerSales[OrderQuantity])",              F_Sales, "#,0");
Make("Avg Order Value",     "DIVIDE([Sales],[Order Count])",                      F_Sales, "#,0.00");
Make("Avg Unit Price",      "DIVIDE([Sales],[Units Sold])",                       F_Sales, "#,0.00");
Make("Avg Unit Cost",       "DIVIDE([Total Cost],[Units Sold])",                  F_Sales, "#,0.00");
Make("Profit per Unit",     "DIVIDE([Gross Profit],[Units Sold])",                F_Sales, "#,0.00");
Make("Profit per Order",    "DIVIDE([Gross Profit],[Order Count])",               F_Sales, "#,0.00");

// CUSTOMER INSIGHTS
Make("Distinct Customers",  "DISTINCTCOUNT(FactResellerSales[CustomerKey])",      F_Cust,  "#,0");
Make("Sales per Customer",  "DIVIDE([Sales],[Distinct Customers])",               F_Cust,  "#,0.00");
Make("Orders per Customer", "DIVIDE([Order Count],[Distinct Customers])",         F_Cust,  "#,0.00");
Make("Units per Customer",  "DIVIDE([Units Sold],[Distinct Customers])",          F_Cust,  "#,0.00");
Make("Profit per Customer", "DIVIDE([Gross Profit],[Distinct Customers])",        F_Cust,  "#,0.00");

// PRODUCT PERFORMANCE
Make("Distinct Products",   "DISTINCTCOUNT(FactResellerSales[ProductKey])",       F_Prod,  "#,0");
Make("Sales per Product",   "DIVIDE([Sales],[Distinct Products])",                F_Prod,  "#,0.00");
Make("Profit per Product",  "DIVIDE([Gross Profit],[Distinct Products])",         F_Prod,  "#,0.00");
Make("Sales Category Share","DIVIDE([Sales], CALCULATE([Sales], ALL('DimProductCategory')))",          F_Prod, "0.00%");
Make("Profit Category Share","DIVIDE([Gross Profit], CALCULATE([Gross Profit], ALL('DimProductCategory')))", F_Prod, "0.00%");

// TIME INTELLIGENCE
// Make sure DimDate is marked as Date table and FullDateAlternateKey is a Date type
var dateCol = "'DimDate'[FullDateAlternateKey]";
Make("Sales YTD",          "TOTALYTD([Sales], " + dateCol + ")",                  F_Time, "#,0");
Make("Profit YTD",         "TOTALYTD([Gross Profit], " + dateCol + ")",           F_Time, "#,0");
Make("Sales PY",           "CALCULATE([Sales], DATEADD(" + dateCol + ", -1, YEAR))",        F_Time, "#,0");
Make("Profit PY",          "CALCULATE([Gross Profit], DATEADD(" + dateCol + ", -1, YEAR))", F_Time, "#,0");
Make("Sales YoY %",        "DIVIDE([Sales]-[Sales PY],[Sales PY])",               F_Time, "0.00%");
Make("Profit YoY %",       "DIVIDE([Gross Profit]-[Profit PY],[Profit PY])",      F_Time, "0.00%");
Make("Sales MTD",          "TOTALMTD([Sales], " + dateCol + ")",                  F_Time, "#,0");
Make("Profit MTD",         "TOTALMTD([Gross Profit], " + dateCol + ")",           F_Time, "#,0");
Make("Sales QTD",          "TOTALQTD([Sales], " + dateCol + ")",                  F_Time, "#,0");
Make("Profit QTD",         "TOTALQTD([Gross Profit], " + dateCol + ")",           F_Time, "#,0");
Make("Sales Rolling 12M",  "CALCULATE([Sales], DATESINPERIOD(" + dateCol + ", MAX(" + dateCol + "), -12, MONTH))",         F_Time, "#,0");
Make("Profit Rolling 12M", "CALCULATE([Gross Profit], DATESINPERIOD(" + dateCol + ", MAX(" + dateCol + "), -12, MONTH))",  F_Time, "#,0");

// summary to Output window
Console.WriteLine("Created: " + created + ", Updated: " + updated);

    This is better; they are neatly organised in folders and have formatting applied. All I need to do now is review them and save them. The only thing that didn’t work was the formatting.

    Now that it worked, we’ll ask for some more measures: 

    // -----------------------------------------------------------------------------
// AdventureWorksDW 2022 – Extended Measure Library
// Creates ±35 measures for FactResellerSales with proper folders & formatting.
// -----------------------------------------------------------------------------

var table = Model.Tables["FactResellerSales"];
if (table == null) throw new Exception("Table 'FactResellerSales' not found.");

// helper delegate
System.Action<string,string,string> Make = (name, expr, folderPath) =>
{
    var m = table.AddMeasure(name, expr);
    m.DisplayFolder = folderPath;
};

// -----------------------------------------------------------------------------
// SALES PERFORMANCE
// -----------------------------------------------------------------------------
Make("Sales",            "SUM(FactResellerSales[SalesAmount])",              "Measures\\Sales Performance");
Make("Total Cost",       "SUM(FactResellerSales[TotalProductCost])",         "Measures\\Sales Performance");
Make("Gross Profit",     "[Sales] - [Total Cost]",                            "Measures\\Sales Performance");
Make("Gross Margin %",   "DIVIDE([Gross Profit],[Sales])",                    "Measures\\Sales Performance");
Make("Order Count",      "DISTINCTCOUNT(FactResellerSales[SalesOrderNumber])","Measures\\Sales Performance");
Make("Units Sold",       "SUM(FactResellerSales[OrderQuantity])",             "Measures\\Sales Performance");
Make("Avg Order Value",  "DIVIDE([Sales],[Order Count])",                     "Measures\\Sales Performance");
Make("Avg Unit Price",   "DIVIDE([Sales],[Units Sold])",                      "Measures\\Sales Performance");
Make("Avg Unit Cost",    "DIVIDE([Total Cost],[Units Sold])",                 "Measures\\Sales Performance");
Make("Profit per Unit",  "DIVIDE([Gross Profit],[Units Sold])",               "Measures\\Sales Performance");
Make("Profit per Order", "DIVIDE([Gross Profit],[Order Count])",              "Measures\\Sales Performance");

// -----------------------------------------------------------------------------
// CUSTOMER INSIGHTS
// -----------------------------------------------------------------------------
Make("Distinct Customers", "DISTINCTCOUNT(FactResellerSales[CustomerKey])",  "Measures\\Customer Insights");
Make("Sales per Customer", "DIVIDE([Sales],[Distinct Customers])",           "Measures\\Customer Insights");
Make("Orders per Customer","DIVIDE([Order Count],[Distinct Customers])",     "Measures\\Customer Insights");
Make("Units per Customer", "DIVIDE([Units Sold],[Distinct Customers])",      "Measures\\Customer Insights");
Make("Profit per Customer","DIVIDE([Gross Profit],[Distinct Customers])",    "Measures\\Customer Insights");

// -----------------------------------------------------------------------------
// PRODUCT PERFORMANCE
// -----------------------------------------------------------------------------
Make("Distinct Products",   "DISTINCTCOUNT(FactResellerSales[ProductKey])",  "Measures\\Product Performance");
Make("Sales per Product",   "DIVIDE([Sales],[Distinct Products])",           "Measures\\Product Performance");
Make("Profit per Product",  "DIVIDE([Gross Profit],[Distinct Products])",    "Measures\\Product Performance");
Make("Top Product Sales",   "TOPN(1, VALUES('DimProductSubcategory'[EnglishProductSubcategoryName]), [Sales])", "Measures\\Product Performance");
Make("Sales Category Share","DIVIDE([Sales], CALCULATE([Sales], ALL('DimProductCategory')))", "Measures\\Product Performance");
Make("Profit Category Share","DIVIDE([Gross Profit], CALCULATE([Gross Profit], ALL('DimProductCategory')))", "Measures\\Product Performance");

// -----------------------------------------------------------------------------
// TIME INTELLIGENCE  (requires DimDate marked as Date Table)
// -----------------------------------------------------------------------------
Make("Sales YTD",        "TOTALYTD([Sales],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Profit YTD",       "TOTALYTD([Gross Profit],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Sales PY",         "CALCULATE([Sales],DATEADD('DimDate'[FullDateAlternateKey],-1,YEAR))", "Measures\\Time Intelligence");
Make("Profit PY",        "CALCULATE([Gross Profit],DATEADD('DimDate'[FullDateAlternateKey],-1,YEAR))", "Measures\\Time Intelligence");
Make("Sales YoY %",      "DIVIDE([Sales]-[Sales PY],[Sales PY])", "Measures\\Time Intelligence");
Make("Profit YoY %",     "DIVIDE([Gross Profit]-[Profit PY],[Profit PY])", "Measures\\Time Intelligence");
Make("Sales MTD",        "TOTALMTD([Sales],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Profit MTD",       "TOTALMTD([Gross Profit],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Sales QTD",        "TOTALQTD([Sales],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Profit QTD",       "TOTALQTD([Gross Profit],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Sales Rolling 12M","CALCULATE([Sales],DATESINPERIOD('DimDate'[FullDateAlternateKey],MAX('DimDate'[FullDateAlternateKey]),-12,MONTH))","Measures\\Time Intelligence");
Make("Profit Rolling 12M","CALCULATE([Gross Profit],DATESINPERIOD('DimDate'[FullDateAlternateKey],MAX('DimDate'[FullDateAlternateKey]),-12,MONTH))","Measures\\Time Intelligence");

    Now we have 4 sets of measures neatly placed in 4 folders.

    Now i can review the numbers.

    TMDL

    TMDL is Microsoft’s YAML-based representation of a tabular model, which is fully editable, meaning you can define:
    1. Tables
    2. Columns
    3. Measures
    4. Relationships
    5. Calculation groups
    You can use it in the Tabular editor and Power BI Desktop.

    Right-click on the Reseller table and select script TMDL to Script tab:

    Here we can add in measures as follows, before the partition block.
    Here, we can define the expression, the format string, and the display folder, which will live in the FactReseller table.
    And it worked nicely.

  • DAX Server Timings in DAX Studio

    We can take a measure like the one customer order measure, add it to a card visual, and use the performance analyzer to give us the DAX query, which we can paste into the performance analyzer. However, we may see different results if we use it in a matrix, so we will test that too.

    The original DAX measure is as follows, and the test measures are found here, which use different functions to create the virtual tables used in the methods. The first measure is below, and we will look at the output of the server timings in more detail, using AI to help us analyze the output.

    CustomersWithOneOrder = 
VAR _CustomerOrders = SUMMARIZE('FactInternetSales', -- Table 
                            'FactInternetSales'[CustomerKey], -- Groupby  
                             "Orders", DISTINCTCOUNT('FactInternetSales'[SalesOrderNumber]) 
                                )
VAR _OneOrder = FILTER(_CustomerOrders, [Orders] = 1)

RETURN
COUNTROWS(_OneOrder)

    The DAX query created by the Formula Engine (copied from the performance analyzer) is as follows for a card visual (total only):

    // DAX Query
EVALUATE
    ROW(
    "CustomersWithOneOrder", '_Measures'[CustomersWithOneOrder]
)


    In DAX Studio, we can paste in the code, clear the cache first, select server time, and all the options to show us each step of the query plan

    The first part of the server timings shows us that the query took 12 ms in the Formula engine (FE) and 5 ms in the storage engine (SE) a total of 12 ms to run. In addition, we can see that a total of 2 SE queries were parsed to the Vertipaq engine.

    This SQL-like language is called xmlSQL and here are the steps that we can see in DAX Studio. 

    -- Line 1, Subclass Internal, Duration 2 rows 0

SET DC_KIND="GEC32";   -- Selecting distinct count strategy DC_KIND
SELECT
    'FactInternetSales'[CustomerKey],
    'FactInternetSales'[SalesOrderNumber]
FROM 'FactInternetSales';

-----------------------------------
Line 2, Subclass: Internal,  Duration 3 rows 27,659   --- builds a datacache projects of the 2 columns it needs

SET DC_KIND="GEC32";
SELECT
    'FactInternetSales'[CustomerKey],
    'FactInternetSales'[SalesOrderNumber]
FROM 'FactInternetSales';

Estimated size: rows = 27,659  bytes = 110,636
---------------------------------
Line 3, Subclass: Internal,  Duration 3, rows 0  ---use the disinct cache count from the data cached.


SET DC_KIND="C32";
SELECT
    'FactInternetSales'[CustomerKey],
    COUNT () 
FROM $DCOUNT_DATACACHE USING ( 'FactInternetSales' ) ;

----------------------------
Line 4, Subclass: Scan, Duration 4, Rows 18,484   --- this is the real storage engine scan with a DCOUNT aggregation, group by customerkey

SET DC_KIND="AUTO";
SELECT
    'FactInternetSales'[CustomerKey],
    DCOUNT ( 'FactInternetSales'[SalesOrderNumber] )
FROM 'FactInternetSales';


Estimated size: rows = 18,484  bytes = 221,808

------------------------------------------

Line 5, Subclass Internal, Duration 1, rows 0   --- auzillary count to help the optimizer (density/cardinatlity hints).

SET DC_KIND="DENSE";
SELECT
    'FactInternetSales'[CustomerKey],
    COUNT () 
FROM 'FactInternetSales';

------------------------------------------

Line 6, Subclass Scan, Duration 1, Rows 18,487  --another quick scan of customerkey likely to get the distinct key

SET DC_KIND="AUTO";
SELECT
    'FactInternetSales'[CustomerKey]
FROM 'FactInternetSales';


Estimated size: rows = 18,487  bytes = 147,896

------------------------------------------

Line 7, Duration 0 refers to the execution metrics

    Note that the actual result is 11619, which is not shown in the server timings. The server timings show the intermediary steps.

    So that was all very interesting, but what do we do with it? Well, first, we can use it to compare measures as follows:

    Card Visual (Total) DAX query performance

    Table function used in measuresFESETotalSE QueriesApprox. Peak Mem Consumption (Bytes)
    SUMMARIZE()1201323407
    SELECTCOLUMS()1071722379
    SUMMARIZECOLUMNS()8101813376
    FILTER()671313357

    In the first test, which was based on the DAX query total on a card visual, all the measures ran very fast, with the summarize() and filter() functions performing faster. So based on this, we might run off and decide to use one of these, but then what if we want to use them in a different visual? Will the performance be the same?

    Matrix Visual DAX Query performance

    Table function used in measuresFESETotalSE QueriesApprox. Peak Mem Consumption (Bytes)
    Summarize()79250329539,884
    Selectcolumns()414990611,968
    SummarizeColumns()192645399,54
    FILTER()247397510,445

    So, as we can see, in the DAX query taken from the matrix test, the SUMMARIZE() function performed very poorly, with the measure that used the SUMMARIZECOLUMNS performing best. As you can see, this adds a whole different level of complexity to testing.

    A solution for this is to use the ISINSCOPE() function to determine the scope of how the measure is being filtered and use it to switch between 2 different measures, but of course that makes the model more complex and is a topic for another day.


  • Testing DISTINCTCOUNT() in DAX

    The DISTINCTCOUNT() function can slow down reports, but if it’s required, then what are the alternatives?


    Test 1: Adventure Works

    -- Method 1: DISTINCTCOUNT()

Customers = DISTINCTCOUNT('FactInternetSales'[CustomerKey])

-- Method 2: COUNTROWS(VALUES()

Customers2 = COUNTROWS(VALUES('FactInternetSales'[CustomerKey]))

-- Method 3: SUMX()

Customers3 = SUMX(VALUES('FactInternetSales'[CustomerKey]), 1)

    The measures all give the same results, but which one is faster?


    We can test these measures by opening the above matrix for all years to give us a bit more data. The results are too close to call, so we need more data to work with.


    Test 2: Contoso Database:
    The FactOnlineSales table in the Contoso data warehouse has over 12 million rows of data, which should give our unique count measures a better run.

    We use the same measure calculations but in the new table as follows:

    CTSO Customers = DISTINCTCOUNT('FactOnlineSales'[CustomerKey])

CTSO Customers2 = COUNTROWS(VALUES('FactOnlineSales'[CustomerKey]))

CTSO Customers3 = SUMX(VALUES('FactOnlineSales'[CustomerKey]), 1)

    We can then test the speed of the measure using the performance analyzer, and DISTINCTCOUNT distinctly loses!

    Why is DISTINCTCOUNT() slower in the test results?

    The brains as SQL BI have a good article about DISTINCTCOUNT() vs. SUMX, and they refer to using DAX Studio to get into the details of what’s going on with the DAX query.
    We can start by using the measure in a simple card visual in Power BI to get the simplest DAX to analyze.

    We can copy the code from the performance analyzer into DAX Studio and select the options for getting our output.
    1. Clear on run (clears the cache each time on run). Note this is session-based so it won’t clear the whole cache for you dataset.

    2. Query plan that relates to the 2 engines: Formula Engine and Storage engine.

    There are two query plans generated from a DAX measure:

    Formula Engine plan — defines logical steps (filters, joins, iterators).
    Storage Engine plan(s) — defines physical scans (which columns, which filters, aggregations).

    If the DAX query is simple, it only needs to run the Formula Engine (FE) plan, but there is less information available in DAX Studio. Note that the storage engine is faster than the formula engine at retrieving results.

    Engine Memory

    Formula EngineMemory TypeDescription
    SEPersistent, compressed memory (the VertiPaq model)The actual stored dataset — physical columns and segments
    FEEphemeral, uncompressed memory (working memory)Virtual tables, intermediate results, cached expressions

    Engine Speed

    EngineType of workSpeedThreadsData format
    Storage Engine (SE)Scanning, filtering, grouping, aggregating⚡ Very fastMulti-threadedCompressed, columnar
    Formula Engine (FE)Evaluating DAX expressions, context transitions, row-by-row logic🐢 SlowerSingle-threadedUncompressed, row-based virtual tables



    If we look at server timings, we can see there is no time for the SE, so the

  • Working with Virtual Tables in DAX Measures

    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!



  • How to use the Window and OFFSET Functions in DAX Measures:


    The Window Function

    To use the Window function, we can start by creating a virtual table of customer sales by sales key using the CALCULATETABLE function and then creating a window on the table to return only the results we want by position. We can make use of DAX query to do this.

    
DEFINE

VAR CustomerSales =  CalculateTable(SUMMARIZECOLUMNS('V_FactOnlineSales'[CustomerKey], "Sales", SUMX('V_FactOnlineSales', V_FactOnlineSales[SalesAmount])), NOT(ISBLANK(V_FactOnlineSales[CustomerKey])))

VAR CustomerSalesWindow = 

    WINDOW(
        1,ABS,3,ABS,
      CustomerSales, 
        ORDERBY([Sales], DESC), 
    )
    
EVALUATE
CustomerSalesWindow


    As you can see from the code above, we start off by creating a table in a variable called CustomerSales, which has a column for CustomerKey and a summarized sales column. The table is filtered, so that the customer is not blank.

    We then create a second variable called ‘CustomerSalesWindow’ to filter our CustomerSales table using the Window function.
    The initial customer sales table is as follows

    We then create the window using the following code:

    
VAR CustomerSalesWindow = 

    WINDOW(
        1,ABS,3,ABS,
      CustomerSales, 
        ORDERBY([Sales], DESC), 
    )

EVALUATE
CustomerSalesWindow


    As you can see the original table is sorted by sales in descending order (we can also use partition, but not In this example).

    The syntax for the window function is as follows:
    WINDOW ( from[, from_type], to[, to_type][, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

    So you can see that we have:
    WINDOW(
    from: 1
    from-type: ABS
    to: 1
    to-type: ABS
    relation: CustomerSales
    ORDERBY([Sales[, DESC))


    As you can see the ORDERBY and PARTITION are very similar to SQL in that it is sorting the sales field in descending order.
    The positions from ABS: 1 to ABS: 3 give us the top 3 sales results.

    Of course, we could have used something like TOPN() function to get the top 3 sales, but if we wanted a specific location e.g. The 3rd to 5th positions, then the WINDOW() function would make it much easier.

    The OFFSET() Function

    The OFFSET() function ‘Returns a single row that is positioned either before or after the current row within the same table, by a given offset’.

    In this example, we use OFFSET() to display the previous month’s sales in a table. We start off by creating a _SummarySales table and then add a second column _SalesOffest, which uses -1 as the delta which shows the previous month’s sales when the ORDERBY clause on CalanderMonth is set to ascending order (ASC). We can then add the final SalesMonthOnMonth column to the table to get the final summary.

    AFDEFINE
	-- Syntax:
	-- OFFSET ( <delta>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )
	
	-- Create Sales by moth table
	
	VAR _SummarySales = SUMMARIZECOLUMNS(
		DimDate[CalendarMonth],
		"Sales", SUMX(
			RELATEDTABLE(FactOnlineSales),
			FactOnlineSales[SalesAmount]
		)
	)

-- Add the offset column by one month

	VAR _SalesOffset =
	ADDCOLUMNS(
		_SummarySales,
		"PreviousMonth", SELECTCOLUMNS(
			OFFSET(
				-1,
				_SummarySales,
				ORDERBY(
					[CalendarMonth],
					ASC
				)
			),
			[Sales]
		)
	)

-- Add a month on month difference column

	VAR _SalesMonthOnMonth =
	ADDCOLUMNS(
		_SalesOffset,
		"Month on Month", [Sales] - [PreviousMonth]
	)

EVALUATE
	_SalesMonthOnMonth

    After adding the previous month column, we can then add a month-on-month column.
    This gives us a table as follows:



  • How to filter DAX Measures In Power BI

    The application of various filter arguments is crucial to controlling the output of DAX measures when visual filters are present on a report page. When CALCULATE() is used in an expression, any filters applied will override any existing filters on the filter being applied.
    e.g. CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimProduct[BrandName]=”Litware”)
    Any filter on V_DimProduct[BrandName]is removed and the new filter is applied. So a slicer on that column would stop working.

    In this example, we create a number of measures in a matrix, using different filter functions. There are 3 slicers that can be applied to the matrix: Year, BrandName, and ProductCategoryName. The original simple measure is sales amount, which is simply just a sum of sales amount from the FactOnlineSales table from the Contoso Retail Datawarehouse.



    Simple SUMX Measure:     The original measure with no filter arguments, will just be affected by the current filter context applied by both slicers and matrix or table rows and columns
    Sales Amount =
    SUM(V_FactOnlineSales[SalesAmount])

    CALCULATE(): Using the powerful Calculate() function, we can apply filters to the original measure to change the way the visual filters affect the measure.

    Sales Amount (Filter Brand Litware) =CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimProduct[BrandName]=”Litware”)

    In the next example, we apply a filter using calculate, but on the year, which is a filter in the matrix. As you can see, the filter on year is removed from the columns and the sum of sales for 2008 is repeated for each year from 2007 to 2009.

    Sales Amount (Filter Year 2008) = CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimDate[CalendarYear]=2008).

    To fix this issue we can use KEEPFILTERS()

    KEEPFILTERS()    : The KEEPFILTERS() function helps us keep the filters on Year in the matrix:
    Sales Amount (KeepFilters) =

    CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimDate[CalendarYear]=2008, KEEPFILTERS(V_DimDate))

    REMOVEFILTERS(): By using the REMOVEFILTERS() function along with CALCULATE() we can remove all the filters applied to the report. As you can see in the table above, this removes the filters from the Year, Brand, and Product Category Name columns. Essentially giving us the total sales for the company for all time.

    Sales Amount (RemoveFilters: all) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), REMOVEFILTERS())

    REMOVEFILTERS(‘TableName’    ): We can also use the REMOVEFILTERS() function to remove filters only on a specific table (not all tables). In this example, we remove any filters on the V_DimProduct table. Other filters will continue to filter the measure:

    Sales Amount (RemoveFilters: Table) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), REMOVEFILTERS(V_DimDate))

    REMOVEFILTERS(‘TableName'[ColumnName]): A more granular method is to remove filters just on specific columns. In this example, we remove any filtering applied by V_DimProductCategory'[ProductCategoryName]

    Sales Amount (RemoveFilters: Column) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), REMOVEFILTERS(‘V_DimProductCategory'[ProductCategoryName]))

    ALL(‘TableName’): Removes all filters from a specified table. This is similar to REMOVEFILTERS(‘TableNAme’), but it works differently.

    Sales Amount (All: DimProductCategory Table) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), ALL(V_DimProductCategory))

    ALL(‘ColumnName’): Remove all filters from specified columns:

    ALLEXCEPT(): Remove all filters except the specified column or columns. In this example, we remove all filters except from the V_DimProductCategory'[ProductCategoryName] column.

    Sales Amount (AllExcept: ProductCategoryName) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), ALLEXCEPT(V_DimProductCategory, ‘V_DimProductCategory'[ProductCategoryName]))

    ALLNONBLANKROWS(): If your data has blank rows in it, then this may be useful to you. If it doesn’t then you don’t need it. You can apply it to a table or columns:

    SalesTableRows = SalesTableRows (non blank) = COUNTROWS(ALLNOBLANKROW(‘V_FactOnlineSales’))
    SalesOrderLines (non blank) = COUNTROWS(ALLNOBLANKROW(‘V_FactOnlineSales'[SalesOrderLineNumber]))

    ALLSELECTED(‘TableName’): Removes filters coming from within the visual. So in this example, ALLSELECTED() removes the filters on year within the matrix i.e the columns, but if you use the slicer on year, it will still work. You can also apply to columns.

    Sales Amount (AllSelected DimDate) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), ALLSELECTED(V_DimDate))

    Using multiple Tables and Columns in Filter Expressions

    The above use of filter functions affecting visuals can be further expanded to include multiple tables and multiple columns as in the examples below:

    CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimProduct[BrandName]=”Litware” &&  V_DimProduct[BrandName]=”Northwind Traders”)

    Sales Amount (Filter Multiple Year) =

    CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimDate[CalendarYear] IN {2008, 2009})

    Sales Amount (RemoveFilters multiple) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), REMOVEFILTERS(‘V_DimProductCategory'[ProductCategoryName], ‘V_DimProductCategory'[ProductCategoryDescription]))

    Sales Amount (All: multiple) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), ALL(V_DimProductCategory), ALL(V_DimDate))

    Recommended Book: The Definitive Guide to DAX by Marco Russo and Alberto Ferrari













  • How to Create Multiple Measures With Power BI DAX Query Editor

    Creating multiple measures in DAX Query Editor

    It may be useful to create templates for creating DAX models, using commonly used DAX measures. Here we create a template for commonly used sales measures, such as Sales YTD, Sales Last Year, Sales Year on Year, and Sales Year on Year %, we can then apply the same logic for QTD and MTD.

    The full code for creating the measures, I will add to the bottom of this page.

    For this example, I am using just 2 tables from the Contoso Retail Data warehouse: DimDate and FactSales. They are joined on the DateKey.

    We start with the Year sales measures as shown below in the DAX query editor. To add them to the model, we just click ‘Update model: Add new measure’, but first we want to format the code, using the Format Query button.

    Here is the code, with the DAX formatted.

    We can then click the 4 ‘Update mode: Add new measure’ texts and it will add the 4 measures to the model..


    We can then create similar measures for QTD and MTD as follows:

    Here is the code for the Quarterly measures:

    The code for creating the Monthly measures is as follows:

    That gives me the 12 measures in record time!


    As promised here is the full code that can be copied and pasted. Of course, you’ll need to change the table names as required. Note, I have created an empty ‘_Meusures’ table to act as a container for the measures.

    // Learn more about DAX queries at https://aka.ms/dax-queries
DEFINE
//Year Measures
	MEASURE '_Measures'[Sales_YTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			DATESYTD('DimDate'[Datekey])
		)
	MEASURE '_Measures'[Sales_LY_YTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			SAMEPERIODLASTYEAR(DATESYTD('DimDate'[Datekey]))
		)
	MEASURE '_Measures'[Sales_YOY] = '_Measures'[Sales_YTD] - '_Measures'[Sales_LY_YTD]
	MEASURE '_Measures'[Sales_YOY%] = ('_Measures'[Sales_YTD] - '_Measures'[Sales_LY_YTD]) / '_Measures'[Sales_LY_YTD]
	
//QTD Measures
	MEASURE '_Measures'[Sales_QTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			DATESQTD('DimDate'[Datekey])
		)
	MEASURE '_Measures'[Sales_LY_QTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			SAMEPERIODLASTYEAR(DATESQTD('DimDate'[Datekey]))
		)
	MEASURE '_Measures'[Sales_QTD_YOY] = '_Measures'[Sales_QTD] - '_Measures'[Sales_LY_QTD]
	MEASURE '_Measures'[Sales_QTD_YOY%] = ('_Measures'[Sales_QTD] - '_Measures'[Sales_LY_QTD]) / '_Measures'[Sales_LY_QTD]
	
	//MTD Measures
	MEASURE '_Measures'[Sales_MTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			DATESMTD('DimDate'[Datekey])
		)
	MEASURE '_Measures'[Sales_LY_MTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			SAMEPERIODLASTYEAR(DATESMTD('DimDate'[Datekey]))
		)
	MEASURE '_Measures'[Sales_MTD_YOY] = '_Measures'[Sales_MTD] - '_Measures'[Sales_LY_MTD]
	MEASURE '_Measures'[Sales_MTD_YOY%] = ('_Measures'[Sales_MTD] - '_Measures'[Sales_LY_MTD]) / '_Measures'[Sales_LY_MTD]

  • How to use the Index Function in DAX Measures

    Demonstrating the use of the Index function with the Contoso Retail Data warehouse. We can start off by building a virtual table in the DAX query editor, which we can use to apply the Index function. The table creates is a list of the first 10 customers by customer key from the DimCustomer table.

    DEFINE
	-- Syntax
	-- INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

	VAR CustomerSalesSample =
	CALCULATETABLE(
		SELECTCOLUMNS(
			DimCustomer,
			"CustomerKey", DimCustomer[CustomerKey],
			"FirstName", DimCustomer[FirstName],
			"LastName", DimCustomer[LastName],
			"Total Sales", SUMX(
				RELATEDTABLE(FactOnlineSales),
				FactOnlineSales[SalesAmount]
			)
		),
		TOPN(
			10,
			DimCustomer,
			DimCustomer[CustomerKey],
			ASC
		)
	)

VAR Ind = INDEX(
		1,
		CustomerSalesSample,
		ORDERBY(
			[Total Sales],
			DESC
		)
	)
EVALUATE
	Ind

    If we evaluate the CustomerSalesSample table first we can see the table we are working with.

    The syntax for the INDEX function is as follows:

    — INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )
    In our example, we use the <position> of 1 to get the first sales amount, which is the highest sales amount as the Total Sales column is sorted in descending order (DESC)

    And then when we evaluate the Index expression (variable Ind), we get the following, which is the correct output we are looking for.

    To get the last position, we could either sort the data by ascending order (ASC) or we can use the INDEX of -1 as the following example:

    VAR Ind = INDEX(
		-1,
		CustomerSalesSample,
		ORDERBY(
			[Total Sales],
			DESC
		)
	)

    When we use -1 as the index we get the blank sales returned, which isn’t what we wanted, so need to modify the code.

    One way of filtering out the blanks from the sales table is to add a filter on the table to filter out the blanks as an additional variable as int he below.

    DEFINE
	-- Syntax
	-- INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

	VAR CustomerSalesSample =
	CALCULATETABLE(
		SELECTCOLUMNS(
			DimCustomer,
			"CustomerKey", DimCustomer[CustomerKey],
			"FirstName", DimCustomer[FirstName],
			"LastName", DimCustomer[LastName],
			"Total Sales", SUMX(
				RELATEDTABLE(FactOnlineSales),
				FactOnlineSales[SalesAmount]
			)
		),
		TOPN(
			10,
			DimCustomer,
			DimCustomer[CustomerKey],
			ASC
		) 

	)
	VAR CustomerSalesNotBlank = FILTER(CustomerSalesSample, NOT(ISBLANK([Total Sales])))	

	VAR Ind = INDEX(
		-1,
		CustomerSalesNotBlank,
		ORDERBY(
			[Total Sales],
			DESC
		)
	)
EVALUATE
 Ind

    Evaluating this code now gives us the least total sales amount that is not blank:

  • How to use the RankX Function in DAX Measures

    Using the RANK() Function in DAX

    To demonstrate the use of the DAX function, we’ll start by creating a simple sales table with the sales amount and the year. Then we’ll add additional columns to create examples of using the RANK function.

    In the first example (SimpleSalesRank), we’ll just create a simple ranking. The default function ranks the Sales column from the least amount to the highest amount and you’ll notice the blank sales value is included as rank one. Note, we can order by more than one column if partitioning by more than one column.

    SimpleSalesRank = RANK(ORDERBY(SalesRank[Sales]))

    The first thing we can do is move the blank value to the end of the ranking, using the LAST parameter.

    SimpleSalesRankBLANKLast = RANK(ORDERBY(SalesRank[Sales]), LAST)

    Then we can rank the sales values from the highest first, while still retaining blank in the last position.

    SimpleSalesRankDESC0 = RANK(ORDERBY(SalesRank[Sales], DESC), LAST)

    We can then partition the ranking by using the Year column.

    SimpleSalesRankPARTITION = RANK(DENSE, ORDERBY(SalesRank[Sales], DESC),  LAST, PARTITIONBY(SalesRank[Year]))

    Using RANKX() function in DAX

    In the next example, we create a similar Sales table with a Sales column and a year column. We can then use the RANK functions to create our rankings.

    First, we’ll create a simple ranking as before, but with RANKX(). Included here is the function syntax in the comments.
    As you can see from the table above, the RANKX function defaults to sorting the highest sales value first, whereas the RANK function sorts it last. The RANKX function also defaults to putting the blank last, whereas the RANK function ordered it first.

    RankXFunction = 
--RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])  
RANKX(SalesRankX2, SalesRankX2[Sales])

    We can reverse the order with the following code, but you’ll notice we don’t use the ORDERBY clause in the code:

    RankXFunctionASC = RANKX(SalesRankX2, SalesRankX2[Sales],,  ASC)

    We can also apply the DENSE clause for the ties, as the default is to SKIP the ranking when there have been equal values, for example, there are 2 sales of value 94, which are both ranked 6, as the default treatment of ties is set to SKIP, the next rank value jumps to 8. With the DENSE clause, the next rank does not jump.

    RankXFunctionDENSE = 
--RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])  
--note: defaul ties is SKIP
RANKX(SalesRankX2, SalesRankX2[Sales], ,  ASC, DENSE)

    In the next example we use RANKX with RELATEDTABLE(). We start of by creating a sample of the DimCustomer table, joining it to the FactOnline Sales table and then adding a ranking table to the new customer table for total sales. We then check the ranking by adding a totalsales

    Step 1: Create a sample customer table. In the core below, we create a simple selection of CustomerKey, FirstName, LastName and then filter the table by the first 10 customers by customerkey. So this will give us a table of the first 10 customers in the DimCustomer table. These data come from the Contoso Retail Data Warehouse.

    CustomerSimple = CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName]
    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

    The table below is created (first 3 columns), then we add the SalesRank and CustomerTotalSalesCheck to demonstrate the use of the RANKX function with RELATEDTABLE function

    The code for ranking for sales is below. Then we add the CustoemrTotalSalesCheck to confirm that our ranking is working correctly.
    As you can see it is. The first rank of 1 is allocated to total sales of £3,932. The last value is blank ranked 10. You will notice there is no rank 8, so the default tie is to SKIP rank when then is a tie. We can change this by adding the DENSE clause 

    SalesRank = RANKX(CustomerSimple, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]))

    Add the the CustomerTotalSales Check column:

    CustomerTotalSalesCheck = SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount])

    Next, we add the DENSE so the ties don’t jump (optional)

    SalesRankDENSE = RANKX(CustomerSimple, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense)

    Creating a Measure using RANKX()
    Now we have some good examples of using the RANK and RANKX function we can use them in a measure, which creates a temporary table in memory.

    To create the measure we can use the DAX Editor in Power BI, DAX Tabular Editor, or DAX Studio. My preference is the DAX Tabular editor, but the code will work in all three, allowing us to see the data as we build the measure. If you just try and build the measure, you can’t be sure what’s going on unless you build the table directly in Power BI, but they are slower to run.

    Here we borrow the code from the previous example creating a physical table, but ensure the table is set to DimCustomer in the Rank function.

    DEFINE
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"Rank", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

EVALUATE
CustomerSample

    Here is the output of the code in DAX Query View (inside Power BI, I haven’t used tabular editor this time).

    As you can see the top rank is 3932 as before.

    I can now for example filter the rankings table, in this example, I filter greater than 1 and less than 4

    DEFINE
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"RankNumber", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

VAR TopThreeRankSales = FILTER(CustomerSample, AND([RankNumber] > 1, [RankNumber]< 4))

EVALUATE
TopThreeRankSales

    This gives me a new table:

    Next, we want to get the sum of the 2nd and 3rd-ranking sales, we can do this by using the GROUPBY function, but without grouping on any columns.

    DEFINE
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"RankNumber", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

VAR TopThreeRankSales = FILTER(CustomerSample, AND([RankNumber] > 1, [RankNumber]< 4))
VAR SalesOfSecondAndThird = GROUPBY(TopThreeRankSales, "SalesOfSecondAndThird", SUMX(CURRENTGROUP(), [TotalSalesCheck]))

EVALUATE
SalesOfSecondAndThird

    This gives the following output:

    We should now be able to use the DAX in a measure, so we transfer the DAX code into a Measure as follows:
    The code using DEFINE and EVALUATE is a DAX query in the DAX query editor. When we create a measure we are creating a DAX expression. DAX expressions are converted to DAX queries when they are evaluated. Here is the code for creating the measure below.

    _SecondAndThirdTotalSales = 
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"RankNumber", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

VAR TopThreeRankSales = FILTER(CustomerSample, AND([RankNumber] > 1, [RankNumber]< 4))
VAR SalesOfSecondAndThird = GROUPBY(TopThreeRankSales, "SalesOfSecondAndThird", SUMX(CURRENTGROUP(), [TotalSalesCheck]))

RETURN
SalesOfSecondAndThird

    We can then use the measure in a visualization as follows:

  • Useful Power BI DAX Code Examples

     DAX Code Examples:
    1. Using Variables
    2. FORMAT()
    3. HASONEVALUE()
    4. AND, &&
    5. CALCULATETABLE() and SUMMARIZE()
    6. USERELATIONSHIP()
    7. SWITCH()
    8. ISFILTERED() and making visual transparent
    9. SELECTEDVALUE() and creating a dynamic Graph Title
    10. FILTER and ADDCOLUMNS
    11. RANK()

    VAR: Using Variables

    Running Total =
    VAR MaxDateInFilterContext = MAX ( Dates[Date] ) //variable 1 max date#
    VAR MaxYear = YEAR ( MaxDateInFilterContext ) //variable 2 year of max date
    VAR DatesLessThanMaxDate = //variable 3 filter dates > variable 1 and variable 2
    FILTER (
    ALL ( Dates[Date], Dates[Calendar Year Number] ),
    Dates[Date] <= MaxDateInFilterContext
    && Dates[Calendar Year Number] = MaxYear
    )
    VAR Result = //variable 4 total sales filtered by variable 3
    CALCULATE (
    [Total Sales],
    DatesLessThanMaxDate
    )
    RETURN
    Result //return variable 4

    FORMAT: Formatting Numbers
    actual = if(sum[actual] >1000000, “FORMAT(SUM([actual], “#, ##M”), IF(SUM([actual]>=1000, “FORMAT(SUM(actual]), “#,,.0K”))

    FORMAT(min(column, “0.0%”)
    FORMAT(min(column, “Percent”)

    eg. if matrix is filtered, 
    IF(ISFILTERED(field], SELECTEDVALUE([column])

    HASONEVALUE: Check if column has one value in if
    Valuecheck = if(HASONEVALUE([column], VALUES(field))


    FILTER table by related field = united states and sumx salesamount_usd
    = SUMX(FILTER( ‘InternetSales_USD’ , RELATED(‘SalesTerritory'[SalesTerritoryCountry]) <>”United States” ) ,’InternetSales_USD'[SalesAmount_USD])

    AND, can also use &&
    Demand =
        SUMX (
            FILTER (
                RELATEDTABLE ( Assignments ),
                AND (
                    [AssignmentStartDate] <= [TimeByDay],
                    [TimeByDay] <= [AssignmentFinishDate]
                )
            ),
            Assignments[Av Per Day]
        )


    CALCULATETABLE, SUMMARIZE
    Calculate Table with Summarize and Filter

    Order Profile =
    CALCULATETABLE (
    SUMMARIZE (
    ‘Sales Table’,
    ‘Sales Table'[Order_Num_Key],
    Customer[Sector],
    “Total Value”, SUM ( ‘Sales Table'[Net Invoice Value] ),
    “Order Count”, DISTINCTCOUNT ( ‘Sales Table'[Order_Num_Key] )
    ),
    YEAR ( DimDate[Datekey] ) = YEAR ( TODAY () )
    )


    )

    USERELATIONSHIP Uses inactive relationship between tables

    CALCULATE (
        [Sales Amount],
        Customer[Gender] = "Male",
        Products[Color] IN { "Green", "Yellow", "White" },
        USERELATIONSHIP ( Dates[Date], Sales[Due Date] ),
        FILTER ( ALL ( Dates ), Dates[Date] < MAX ( Dates[Date] ) )


    SWITCH

    SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
    = SWITCH([Month], 1, “January”, 2, “February”, 3, “March”, 4, “April” , 5, “May”, 6, “June”, 7, “July”, 8, “August” , 9, “September”, 10, “October”, 11, “November”, 12, “December” , BLANK() ) //place on separate lines

    SWITCH with Measure

    = SWITCH(TRUE(), 
            [measure] = “turnover”, [turnover]
            [measure] = “Profit”, “[Profit]

    , BLANK()

    )

    Visuals
    ISFILTERED()
    Check Filtered = ISFILTERED([column])

    Dynamic Visual 

    MakeTransparent = 
    IF([check filtered], “FFFFF00” # returns transparent – note hex 7
    “White”)

    Message = IF([check Filtered), “”, “please select a row”)

    Dynamic Graph Title
    Graph year title = selectedvalue([columnname])& ” –  My graph Title”

    ADDCOLUMNS
    New Table>
    Creates a new table and adds columns to it (and in this case also filters it) 

    2013Sales = FILTER(ADDCOLUMNS(FactInternetSales, “Dates”, FactInternetSales[OrderDate], “Sales2”, SUM(FactInternetSales[SalesAmount])), FactInternetSales[OrderYear=2013)

    RANK by 2 Columns (calculated column)
    Measure = RANKX( Filter(all(‘Table’), ‘Table'[customer] = EARLIER(‘Table'[Customer])), ‘Table'[Txn Number],,DESC, DENSE)

    Creates a rank for each customer based on the txn number for each customer

  • DAX Running Total Quick Measure verus a Human Created Measure

    Dax Running Total

    An example we start of we a sales and date table to crate our running table sales from.
    We start by creating a sales measure as follows:

    Sales = SUM(FactSales[SalesAmount])

    Next we create a quick measure to create the ;Running Total in Sales’ and add it to our table:

    The Code for the Quick Measure ‘Sales running total in Year’ is a follows:

    Sales running total in Year = 
CALCULATE(
	[Sales],
	FILTER(
		ALLSELECTED( 'Date'[Year]),  
		ISONORAFTER( 'Date'[Year], MAX('Date'[Year]), DESC
	)
))


    It looks pretty simple, but there is quite a lot to it, so let’s break it down:

    1. CALCULATE() Syntax: CALCULATE(Expression, [Filter1]…)
    The CALCULATE() function takes the [Sales] measure as the first argument. [Sales] is just the sum of sales.

    Next, we have the main filter argument that filters the [Sales] measure on a range of years from the ‘Date’Year column.
    The filter starts with the FILTER function which takes ALLSELECTED(‘Date'[Year]) as the column to filter on and the
    ISONORAFTER( ‘Date'[Year], MAX(‘Date'[Year]), DESC as the filter argument.

    ALLSELECTED(‘Date'[Year])
    The ALLSELECTED function removes all filters external to the column ‘Date'[Year], so in our table, the Years in the year column will be ignored, so all years in the ‘Date’ table will be included in the column.
    We can prove this by creating another measure:

    CountrowsAllSelectedYear = COUNTROWS(ALLSELECTED( ‘Date'[Year]))


    When added to our table visual, we can see the count is 24 for every year row in the table. That is because this particular table has 24 years of date information in it.

    The next part is the tricky part, how we filter ALLSELECTED(‘Date'[Year])

    ISONORAFTER(‘Date'[Year], MAX(‘Date'[Year]), DESC)
    The ISONORAFTER function compares each element of the first argument (the ‘Date'[Year column] with the second argument, the MAX(‘Date'[Year]), which is either sorted in ascending order (ASC) or descending order (DESC).
    We can check what MAX(‘Date'[Year]) evaluates to with the following measure:

    Max Date Year = MAX(‘Date'[Year])


    We can see in our table that the max date equals the Year in the table visual.

    4.2 The second argument is MAX(‘Date'[Year]). To understand what this function does, we can create a measure as it may not be what you think.

    Max Date Year = MAX(‘Date'[Year])


    As you can see in the table below, the Max Date Year used in this context actually returns the same date as the Year column.


    The comparison works as follows:

    ‘Based on the sort order, the first parameter is compared with the second parameter. If the sort order is ascending, the comparison to be done is the first parameter greater than the second parameter. If the sort order is descending, the comparison to be done is the second parameter less than the first parameter’

    To table below shows what is going on (if I filter the page to years 2007-2009), which effectively filters the years in the table down from 24 to 3, we can create the following table.
    Essentially it is checking if each element in the date year table is on or after the max date, which is derived from the year in the table visual. If it is less than the Max([Date] then it returns true.

    The Years in the Date[Year] column are then returned as a filter to the Calculate to filter the [Sales] measure creating the ‘Running Total in Sales’

    Year: 2007
    Date[Year]Max([Date]) ISONORAFTER(a,a, DESC)
    If first less than second    		Sales Total
    20072007TRUE4.56M
    20082007FALSE
    20092007FALSE
    Running Total4.56M
    Year: 2008Max([Date])ISONORAFTER()Sales Total
    20072008TRUE4.56M
    20082008TRUE4.11M
    20092008FALSE
    Running Total8.63M
    Year :2009Max([Date])ISONORAFTER()Sales Total
    20072009TRUE3.74M
    20082009TRUE4.11M
    20092009TRUE3.74M
    Running Total12.41M


    That’s pretty complicated if you ask me, even though it is correct.
    We can create our own Running Total in Sales Measure, that is easy to understand.
    The code is below:

    Running Total human = 
VAR the = SELECTEDVALUE('Date'[Year])
VAR MaxDateInFilterContext = MAX ('Date'[Year])
VAR DatesLessThanMaxDate =
    CALCULATE([Sales], 'Date'[Year]<= MaxDateInFilterContext)
RETURN
DatesLessThanMaxDate


    And the output looks good.


  • How to calculate Week-to-Date Sales in a DAX Measure

    If you look around the DAX functions, you are going to be disappointed. There is no week-to-date function like there is in Tableau. There are different methods you can google, but let’s create our one here from scratch.
    In this example, we are using the FactOnlineSales table from the Contoso Retail DW.
    Before we start, want can as an option bring the old data into the future, by using the method outlined here. This is not a requirement, but it means we can create live-looking scenarios with sales data up until yesterday.

    To verify the measure we are going to create, we can create a fixed date range measure that we can compare our dynamic measure to:

    OnlineSales WTD check = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], DATE(2023, 12, 11), DATE(2023, 12, 15)))

    This measure uses the DATESBETWEEN() function as the filter for the CALCULATE() function. Note, that sales will be inclusive from the start to the end dates. You can change the date range to reflect the current week you are in. The dates used in this example are reflective of the time when I created the function.

    To create a dynamic measure, we can start by adding a new measure OnlineSales WTD (week to date).
    Let’s start by adding in the TODAY() function

    OnlineSales WTD = TODAY()-1
    We are going to use the TODAY()-1 as we usually want the data up until the end of the previous day and not to show any partial data for the current day that may be imported to the database.
    If we add it to a card visual, we can verify the output as today-1 (at the time of writing):

    Next, we can use the WEEKDAY function to get the day number from today. We also use variables here, to tidy up the code:

    OnlineSales WTD =
    VAR ThisDay = TODAY()-1
    VAR ThisDayNum = WEEKDAY(currentday)
    RETURN
    currentdayNum

    The measure now displays 6. Today is the 16th of November 2023 (at the time of writing), which is a Saturday, but we added the -1, which is Friday, so we want it to display 5. To get the WEEKDAY() function to start on a Monday, we can add 2 as the second argument, which sets the first day of the week (1) to Monday and the last day of the week to Sunday (7). You can change this according to your needs.

    OnlineSales WTD =
    VAR ThisDay = TODAY()-1
    VAR ThisDayNum = WEEKDAY(currentday, 2)
    RETURN
    ThisDayNum

    This now gives us 5, which is what we want:

    Next, we want to calculate the first day of the week, which we can do as follows:

    OnlineSales WTD =
    VAR ThisDay = TODAY()-1
    VAR ThisDayNum = WEEKDAY(currentday, 2)
    VAR FirstDayOfWeek = thisday -(thisdayNum-1)
    RETURN
    FirstDayOfWeek

    So now we have the logic for calculating the first day of the week and today. So we want to calculate the sales between these 2 dates. So let’s try and use this logic to create a dynamic WTD measure:

    OnlineSales WTD =
    VAR ThisDay = TODAY()-1
    VAR ThisDayNum = WEEKDAY(Thisday,2)
    VAR FirstDayOfWeek = Thisday -(ThisDayNum-1)
    VAR MonSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], ThisDay, ThisDay))
    VAR WTDSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], FirstDayOfWeek, ThisDay))
    RETURN
    IF(ThisDayNum=1, MonSales,
    IF(ThisDayNum>1, WTDSales))

    The code uses two different calculations for WTD. If the previous day is a Monday, it just calculates the Monday sales, if it is after Monday, it calculates the WTD sales

    Now let’s say we want to go back to any point in time and get the Week to Date sales, we can tweak the code as follows:

    OnlineSales WTD =
    VAR ThisDay = MAX('Date'[Date])-1
    VAR ThisDayNum = WEEKDAY(Thisday,2)
    VAR FirstDayOfWeek = Thisday -(ThisDayNum-1)
    VAR MonSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], ThisDay, ThisDay))
    VAR WTDSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], FirstDayOfWeek, ThisDay))
    RETURN
    IF(ThisDayNum=1, MonSales,
    IF(ThisDayNum>1, WTDSales))

    Now if we add the day filter and select today’s date, the week-to-date measure should still match the check, but we can also go back to any point in time and calculate week-to-date.


    Lastly, to calculate the previous year week to date sales, we can create a measure based on our existing OnlineSales WTD measure, but use the SAMEPERIODLASTYEAR() function, to calculate the previous year’s week-to-date, without having to re-rewrite all the code.

    OnlineSales WTD Prev Year = CALCULATE([OnlineSales WTD], SAMEPERIODLASTYEAR('Date'[Date]))