Creating Measures in DAX vs. Qlik Script

Measures are going to determine what calculations we can ultimately show in our visualisations. Whether creating Power BI Measures in Dax or Qlik Sense Measures using Qlik’s script, the result is the same but the way you get there is very different.  Read on to find out more.

This article is part of a larger series of articles comparing Qlik Sense and Power BI.

Read More: Qlik Sense vs Power BI – The Series

Power BI: Measures in DAX

Measures are written using the DAX formula language.  DAX stands for Data Analysis Expressions and is primarily a formula and query language.  It is the language used in PowerBI Desktop, PowerBI.com, PowerPivot and Microsoft SQL Server Analysis services.  One of the benefits of DAX, when you are starting out, is that it has some similarities in the simple functions to the Microsoft Excel Formula language.

Note: PowerBI also uses Power Query Formula Language (also known as “M” or “M Script”) which could be used for column calculation.  M is not the preferred language for calculation, but it is there.  M is primarily used for ETL/Data Shaping tasks and does an incredible job of it.

Qlik Sense: Script Measures

Qlik uses a proprietary scripting language for both data shaping and calculations/measures.  The language is based on the open source language Lua and powers both QlikView and Qlik Sense.  I will refer to it simply as Qlik Script.

Intellisense / Autocompletion

Both tools include some level of working auto-completion when creating calculations.   Microsoft has not yet implemented IntelliSense for M Script in the Query Editor though it is rumoured to be only months away.

Case Sensitivity:

DAX is not case sensitive regarding syntax though it does have functions that are case sensitive regarding the data analysed.

M-Script is fully (read: Painfully) case sensitive.  Once IntelliSense is implemented, M-script will be much easier to write without errors.

Qlik Script is case sensitive on object names such as fields.  Changing the field reference TestPickNull to TestpickNull changes the result of an equation as Qlik does not recognise the later.  The Intellisense feature means you don’t have to worry about this much.  In the case of functions, SUM and sum produce the same result.

The tests:

To test each tool in an apple to apple approach I chose the following conventional measures to calculate in both tools:

Measure 1:  Revenue – This will be a simple sum of the invoice values.  It must be able to be grouped by fiscal period.

Measure 2: Backlog – This is a sum of the orders not shipped.  In this case, we will use this to test each tools ability to check for null values.  The KPI must also be able to be grouped by fiscal period based on an expected ship date field.  We are also testing whether the tools are handling multiple date relationships between 2 tables without issue. In the case of Qlik Sense, this is often handled with a duplicate calendar dimension table.

Measure 3: Bookings-Month in the previous year.  A standard financial requirement.

Measure 4: Average order value – Our data has the value at the line level.  We will aggregate the order value and then average it.

Measure 5: Backlog for customers not in a buying group.  We will test filters within measures as well as the ability to nest existing measures within measures.

 

The actual comparisons DAX vs. Qlik Script

Measure1: Revenue

PowerBI:

Revenue =
SUM ( Invoices[ExtendedPrice] )

It is the same calculation you would do in Excel except you are naming the column that you will sum. One thing to note is that the table “Invoices” is explicitly called out in PowerBI.

Qlik Sense:

Sum(ExtendedPrice)

Note that in Qlik you do not explicitly call out the table in the equation.  Modellers need to be very careful if a data model has 2 or more fact tables with fields with the same name and make the necessary adjustments.

Measure 2: Backlog

PowerBI:

TotBacklog =
CALCULATE (
    SUM ( Backlog[ExtPrice] ),
    USERELATIONSHIP ( Backlog[ExpectedDeliveryDate], ‘Calendar'[Date] ),
Backlog[TestPickNull] = “Y”
)

Qlik Sense:

sum({<[TestPickNull]={Y}>} ExtdPrice)

 

Three differences to deal with in this equation:

Date relationships

In the case of our backlog calculation, there are two dates in the table.  One is the order date, and one is the expected ship date.  If we leave out the Userelationship part of the calculation in PowerBI, the comparison will happen against the order date which will give us a bad value.  This added expression is not required in Qlik because the comparison either occurs in the Visual or the model has multiple calendar dimension tables.  It looks less complicated, but the PowerBI formula provides advantages not apparent in the calculation.

Testing for a null value

Both tools need adjustment to do the null test in a calculation, especially given that it is in a date field.  In the way the tables are set up both are treating the NULL values as blanks.  This null test is solved by adding a column in the table which is Y when the field is null/blank and N when there is a date present.  In both cases then we apply a filter in the calculation.

Both calculate the measure correctly.

The third difference you can’t see

DAX has two methods of creating filters within the Calculate expression.  The first is just by entering the filter after a comma that follows the first expression (as shown above).  The second for more complex filters is within the Filter() expression.  The difference is beyond the scope of the article, but I thought it worth pointing out.

In the case of Qlik, set expressions follow the same syntax throughout.

Measure 3:  Previous Year Orders/Month

The first DAX measure uses built in time intelligence functions.  The built in date functions only work with standard calendars.  You can learn all about Time Intelligence measures here.

Power BI:

Built in DAX: PY-MonthOrders =
CALCULATE ( [Orders], PARALLELPERIOD ( ‘Calendar'[Date], -12, MONTH ) )

This second DAX measure will work with any calendar.

PY-MnthOrders =
IF (
    HASONEVALUE ( ‘Calendar'[Yearmonthnumber] ),
    CALCULATE (
[Orders],
        FILTER (
            ALL ( ‘Calendar’ ),
‘Calendar'[Yearmonthnumber]
= VALUES ( ‘Calendar'[Yearmonthnumber] ) – 12
        )
    ),
    BLANK ()
)

Qlik Sense:

sum({$<[Yearmonthnumber]={“$(=max([Yearmonthnumber])-12)”},
Year=,
Month=,
Quarter=,
Period= >}ExtdPrice)

I always suggest that a calendar table is set up to perform Point in time (the term often used by Qlik users) or Time/Date intelligence (the term often employed by DAX users) calculations.  The fields Calendar[Date], Calendar[Yearmonthnumber], and Yearmonthnumber all are referencing calendar tables in the respective models.

There are some serious differences in the calculations and the more importantly how they are used.

DAX Specific:  As mentioned DAX comes with built in DAX time/date intelligence functions.  If you get into an odd calendar like the 445, you need to avoid these and use expressions such as the one I’ve written which will always work.

READ MORE:  How to do time intelligence in Power BI

Use cases:  The above DAX formula operates in all visuals.  You can put it right next to the current month’s value in a table that has the period as a dimension without any issue.   I’ve attached the table below as an example:

 

 

If you try to do the above with the Qlik expression that I’ve shown, the table will fail.  That is not to say that the expression doesn’t work.  It does, but not if you have a date dimension in the rows. Below is an example of the Qlik version of the expression used in a table where the dimension is StockGroupName.  In this case, the expression works as required.

The Qlik expression I’ve shown will also work perfectly in a KPI visual.

So does that mean Qlik can’t handle the visual with the date row dimension?  No, it just means you need a different formula using the Above( ) expression.   The most basic version of the expression would be:

=above( sum(ExtdPrice),12)

The above() Qlik expression works in this case.  You do need to watch your set expressions with it carefully to ensure that the formula will still work when users start to apply filters.

 

Measure 4:  Average order value

 

PowerBI:

AvgOrder =
CALCULATE (
    AVERAGEX ( VALUES ( Backlog[OrderID] ), [Orders] ),
    ALL ( Backlog[OrderID] )
)

OR

AvgOrder2 = SUM(Backlog[ExtPrice])/DISTINCTCOUNT(Backlog[OrderID])

OR

AvgOrder3 = AVERAGEX(GROUPBY(Backlog,Backlog[OrderID]),Sum(Backlog[ExtPrice]))

Qlik Sense:

Sum( ExtdPrice ) / Count( distinct OrderID )

Or

Avg( Aggr( Sum(ExtdPrice), OrderID ) )

Again both tools handle this equation without any issue, and both have multiple ways that you can calculate it.  Qlik contains the Aggr function which I find very handy and more intuitive that the original method in PowerBI.  With Excel 2016 and PowerBi Desktop, the introduction of the GroupBy function provides similar capabilities.

Measure 5: Backlog for customers not in a buying group.

PowerBI:

BklgNotBG = CALCULATE(Backlog[TotBacklog],Backlog[BuyingGroupID]=0)

Qlik Sense:

sum({<[TestPickNull]={Y}>} ExtdPrice) – sum({<[TestPickNull]={Y}, [BuyingGroupID]={“>0”}>} ExtdPrice)

Nesting measures:

PowerBI can reuse measures that you have already written in new measures.  In this case, we had already calculated the measure [TotBacklog], so there is no point in rewriting the code, you can include that existing measure inside the new measure.  This ability to nest measures is an advantage with DAX.

Qlik Sense does not allow you to reuse your measures.  You find yourself copying old expressions and putting them inside your new expressions.  The lack of nested measures is an odd omission as Qlik’s more mature QlikView product has variable functionality included which allows the nesting of pre-defined variables.

Treatment of null values

In my PowerBI formula, I filtered by BuyingGroupID = 0.  Both tools don’t know what a null is so in this case because the null went into an integer column the nulls are treated as a zero, and this formula works.  I would typically handle this differently, but it illustrates the point.

Qlik did not treat the null as a zero in this case.  I don’t see either case as an advantage, just thought it worth mentioning.

Expression filters / Set Analysis

Set Analysis in Qlik (which are filters applied to specific calculations) have a very detailed syntax which can be a challenge to learn.

Example: {<[TestPickNull]={Y}, [BuyingGroupID]={“>0”}>}

In this case, we are testing for greater than zero, but we can’t write BuyingGroupID>0 because the > bracket means something else so we must say Buying group EQUALS “Greater than zero”.

Writing filters expressions within PowerBI’s ubiquitous Calculate() function is less nit-picky (<– the best term I could come up with) however can become very complicated quickly.  For this reason, we see some of the absolute geniuses in the field change their mind on how a particular formula is written.

I could write an entire post on the differences between Qlik Set Analysis and Power BI DAX Filter Contexts (which I’m not planning right now).  My concise way (I’m not happy with it, so if you’ve got something better, please share) of explaining the differences is that:

• Qlik Set expressions are overall more straight forward.  They feel much more like adding filters in Microsoft Excel.
• DAX filter contexts mean that you must always think regarding tables and often many joined tables.

A final thought on this is that I feel it is harder to learn the easy set expressions in Qlik.  That said, I also believe it is more difficult to learn advanced expressions in DAX.

Other considerations:

Expression Editors:

PowerBI uses a line similar to the formula bar in Excel.  It can be expanded to show additional lines, but ultimately it’s just a space to start typing.

 

 

As a note, I much preferred the original PowerPivot editor seen below which didn’t make it to PowerBI desktop.

 

Read More:  A history of Power BI back to Excel 2010

Qlik Sense uses a basic box seen below which has the list of tables and fields as well as the most basic aggregation functions on the right-hand side.  Again like Power BI, it’s just a space to begin typing.  I like the size but wonder why they haven’t added functionality to aid in the quick creation of expressions.  Another note about the expression editor in Qlik Sense is that it fills the screen and it’s locked.  I often find myself closing it because I want to see something behind it.  It’s a minor thing.

 

Where the expression lives:

Both tools have a very different approach to where measures call home.

Qlik Sense Master Items

Qlik Sense has a master data area which holds measures, dimensions and visuals which would be commonly used by consumers.  If you change the expression in the master data area,  visual elements with links to that measure are updated.

Each visual requires that if you do not link a master measure to it, you create an expression from scratch for that visual.

Power BI Measures associated with tables

Power BI stores Measures with an associated table.   This association is helpful but can also be dangerous.  If you delete a table that has associated measures, the measures will be deleted too.  I’m noticing more and more developers are creating “dummy” tables to associate with their measures, and I find myself leaning this way.  Finding measures in PowerBI is still easy if they are associated to the wrong table as there is a search function available.

In PowerBI, all measures are master measures.  If you change the expression of a measure, all instances will be updated.

Power BI allows the developer to hide measures from report view. Hiding measures can be very handy for nested measures where you only want the top level measure to be seen and put into visualisations by users.

Creating a list of your measures:

Qlik Sense has the list of master measures, but it does not contain a tool to show you a list of all measures used in an APP.  The omission of a list is strange as QlikView has an outstanding Expression Overview tool which shows the expression and where each measure/expression lives.  I’m still notorious for clicking ctrl-alt-E and then staring at the screen when nothing happens.

Power BI does not have a built-in tool to list out your measures.  There is, however, the excellent and free DAX Studio application.  DAX Studio allows you to extract the list of your measures and their associated measures using built in DAX queries.  You can view these queries in Microsoft Excel.  It works very well.

 

 

Summary

Power BI with DAX and Qlik Sense with the Qlik Scripting language both have outstanding capabilities.  They both allow you to create the measures you need to get the job done.  The big difference is in how the tools approach measure creation and some of the niceties that make the job easier.

Which is the better tool for creating measures?  Let me know the winner in the comments and WHY you think so.

Read the rest: Qlik Sense vs Power BI – The Series

Want to get Power BI or Qlik products working in your business.  Set up a free consultation.