Erika Gomez

FROM SQL TO DAX [PART 2]

In Part 1 of this series ‘From SQL to DAX’, we covered all the basic translations, such as SELECTS, grouping and aggregates, JOINs and the WHERE clause… In this second part, we are going to cover specific cases. We’ll go through some functions that are helpful when trying to query your data appropriately. As part of my research I look in forums for common “how to do this SQL query in DAX” type questions to include them in this blog, I hope you find them useful.

Let’s get into it with how to do a SELECT DISTINCT. Let’s assume we are creating a calculated table to get the distinct Invoice Id with the status of the order. In SQL, we would have something like this:

SELECT DISTINCT InvoiceId, 
       InvoiceStatus 
FROM   invoice

In Dax, we have a similar equivalent function: DISTINCT

InvoiceStatusSummary =
DISTINCT (
    SELECTCOLUMNS (
        Invoice,
        "Invoice Number", Invoice[InvoiceId],
        "Status", Invoice[InvoiceStatus]
    )
)

Very straightforward, right? Some of these ‘translations’ from SQL to DAX are as easy as that!

Another useful and easy tool we have in SQL is the CASE Statement, we don’t have an exact equivalent in DAX, but you can use either IF or SWITCH to accomplish the same end result. Consider the following SQL query:

SELECT CASE 
         WHEN ProductName = ‘shirt’ THEN ‘clothing - shirts’ 
         WHEN ProductName = ‘earrings’ THEN ‘jewellery - earrings’ 
         ELSE ProductCategory 
       END AS Category 
FROM   Product

In Dax, using SWITCH:

=
SWITCH (
    Product[Product Name],
    "Shirt", "Clothing - Shirts",
    "Earrings", "Jewellery - Earrings",
    Product[Product Category]
)

In DAX, using nested IF:

=
IF (
    [Product Name] = "Shirt",
    "Clothing - Shirts",
    IF ( [Product Name] = "Earrings", "Jewellery - Earrings", [Product Category] )
)

We will continue the with how to do a IN filtering in DAX. Let’s say we have this SQL code:

SELECT ProductName 
FROM   Product 
WHERE  ProductCategory IN ('Toys', 
                           'Videogames', 
                           'Kids Accessories’)

In DAX, we don’t have this exact operator, however we can workaround this by using nested OR functions:

EVALUATE
CALCULATETABLE (
    VALUES ( Product[Product Name] ),
    OR (
        OR (
            Product[Product Category] = "Toys",
            Product[Product Category] = "Videogames"
        ),
        Product[Product Category] = "Kids Accessories"
    )
)

The same way, you can use the logical operator OR: ||

EVALUATE
CALCULATETABLE (
    VALUES ( Product[Product Name] ),
    Product[Product Category] = "Toys"
        || Product[Product Category] = "Videogames"
        || Product[Product Category] = "Kids Accessories"
)

Now, let’s talk about the EXISTS condition in SQL. This one allows us to determine whether the result of our query has any correlation with the result of a nested query. Let’s see this example:

We want to determine which products have been sold

SELECT ProductId, 
       ProductName 
FROM   Product 
WHERE  EXISTS (SELECT ProductId 
               FROM   Invoice 
               WHERE  Product.ProductId = Invoice.ProductId) 
ORDER  BY ProductName

In DAX we can use a function called ISEMPTY, that is basically the exact opposite of EXISTS and if we combine it with the function NOT we have our desired outcome

EVALUATE
FILTER (
    VALUES ( Product[Product Name] ),
    NOT ISEMPTY ( RELATEDTABLE ( 'Invoice' ) )
)
ORDER BY Product[Product Name]

When we want to compare a string to a specific pattern, we use LIKE in SQL. To do the same operation in DAX we can use a couple of options: FIND (case-sensitive) and SEARCH (not case-sensitive).  Having the following example:

ProductName LIKE ‘%toy%’

When using FIND it can be a little bit faster than SEARCH because it is case sensitivity

FIND(“toy”, Product[ProductName], 1, 0)
SEARCH(“toy”, Product[ProductName], 1, 0)

These 2 functions will return the number that shows the starting point of the text string you want to find. To make it give the True or False value, you can work around this by just adding the comparison at the end:

SEARCH(“toy”, Product[ProductName], 1, 0) > 0
  • If you are using the SEARCH function to look through an entire column and the text is not found in each row you can get an error. Use IFERROR to ensure that a valid result is returned for every row. When the text is not found you’ll get -1. This function makes the statement a lot slower, avoid it if you can.
IFERROR( SEARCH(“toy”, Product[ProductName], 1 ), 0 ) > 0

When having a complex text search, you have to use more wildcards. Consider this condition:

ProductName LIKE %Kids%Fun%Toy%

In Dax:

SEARCH( "Kids*Fun*Toy", Product[ProductName], 1, 0 ) > 0

To finish this blog post I wanted to do a summary of the basic SQL datetime functions in DAX. I’ll go through how to do some of the more commonly used functions.

DATEPART

When trying to obtain a specific part of a date, just like the DATEPART function does in SQL, DAX offers a few functions for each timeframe:

  • YEAR: Returns the Year of a date

YEAR(<date>)

  • QUARTER: Returns the quarter number from 1 – 4

QUARTER(<date>)

  • MONTH: Returns the Month of a date as a number from 1 – 12

MONTH(<datetime>)  

  • WEEKNUM: Return the number of the week for the datetime provided. Has 2 return types: 1) Week begins on Sunday – 2) Week begins on Monday

WEEKNUM(<date>, <return_type>)  

  • WEEKDAY: Returns a number from 1 to 7 identifying the day of the week of a date. Has 3 return types: 1) Week begins on Sunday with 1 to 7 numbers – 2) Week begins on Monday with 1 to 7 numbers and 3) Week begins on Monday with 0 to 6 numbers

WEEKDAY(<date>, <return_type>)   

  • DAY: Return the day of the month from a date as a number from 1 – 31

DAY(<date>)  

The same way you can use HOUR, MINUTE, SECOND to get those values

DATEADD and DATEDIFF

There 2 functions are called the same in DAX and are used in a very similar way, the only difference is that the arguments are provided in a different order

SQL

DATEADD(interval, number, date)

DATEDIFF(interval, date1, date2)

DAX

DATEADD ( <Dates>, <NumberOfIntervals>, <Interval> )

DATEDIFF ( <Date1>, <Date2>, <Interval> )

Now, it’s important to mention that DAX has a very interesting collection of time-intelligence functions that make our life easier when creating our DAX code. I’ll cover this topic in a separated blog as the list of functions is long and I want to get through a few other concepts first, such as DAX engine interpretation, optimization of DAX code and Filter Context (in the meantime, you can find more information here)

The main takeaway I want you to get from this blog post, is that we can find multiple ways to create the results we need in DAX by using a different approaches. And it is at this point when code performance becomes relevant, we need to analyse and consider the size and complexity of the data we are working with as query plans will change in different versions of the DAX engine. We will get into DAX performance and better practices in my next blog post. I hope this one is useful for your transition from SQL to DAX.

Happy Learning!

Leave a comment