Power BI Antipatterns #6: IF and VALUE

What’s VALUE in DAX used for? Do we always need IF in case you want to output 1 and 0 instead of TRUE and FALSE? That’s what this blog post is about.

First of all, here’s today’s formula, which returns 1 in case a date is on or before the last available order date and 0 otherwise:

Example bad = 
VAR LastAvailableDate = MAX(Sales[OrderDateKey])
VAR ThisDate = 'Date'[DateKey]
RETURN
    IF(
        ThisDate <= LastAvailableDate
        ,VALUE("1")
        ,VALUE("0")
    )

Note: while I don’t put commas at the start of lines, they can be useful for debugging, and DAX Studio can temporarily move commas for you in case you need it. Depending on your version of DAX Studio, you may need to enable the Show Debug Commas feature in File > Options > Advanced.

As you might know by now, I prefer concise code, so here are three things I’d get rid of:

  1. VALUE
  2. IF
  3. Varables

VALUE

What does VALUE do? It can convert a text value into a decimal. Is it necessary in our formula? Absolutely not: first, we write numbers as text, then we convert it back to numbers, and the result isn’t even an integer — it’s decimal! So we can safely get rid of VALUE:

Example better 1 = 
VAR LastAvailableDate = MAX(Sales[OrderDateKey])
VAR ThisDate = 'Date'[DateKey]
RETURN
    IF(
        ThisDate <= LastAvailableDate,
        1,
        0
    )

IF

What about IF? If all we want it 0 and 1, do we really need IF? I don’t think so. If you leave ThisDate <= LastAvailableDate by itself, the result will be TRUE or FALSE, so you need to convert the logical values to numbers. INT can do the job in this case:

Example better 2 = 
VAR LastAvailableDate = MAX(Sales[OrderDateKey])
VAR ThisDate = 'Date'[DateKey]
RETURN
    INT(ThisDate <= LastAvailableDate)

Note: INT and CONVERT don’t always work in the same. Read more on SQLBI: Differences between INT and CONVERT in DAX.

Variables

Since each variable is used exactly once, there’s no need to work in different context, and there’s no performance benefit, do we need variables? One might argue they make the formula more readable — I’d say it’s a matter of taste in this case. Personally, I’d get rid of variables:

Example good = INT('Date'[DateKey] <= MAX(Sales[OrderDateKey]))

Just one line and no needless function calls now 🙂

Download the sample file: 6 VALUE.pbix

To be continued…