Tuesday, March 6, 2012

Use DAX Earlier Function in Calculated Column to Mimic Group By Clause

It is very easy to group or slice the measures in the Pivot table by adding a attribute to the row or slicer.  But, how do we create the same effect in the Calculated Column?

image

Here is the Sample Data

image

If we treat this table as a SQL table, we could use the following T-SQL query to find the total queued time for each queue per date.

SELECT [Date], [QueueName], SUM([QueuedTime]) AS [TotalQueuedTime]
FROM CallDetail
GROUP BY [Date], [QueueName]

In PowerPivot, we could use the EARLIER function to achieve the same effect.  EARLIER is a very useful DAX function when you want to use a certain value as input to produce a calculation.


Syntax:



EARLIER(<column>, <number>) 

Solution:


Step 1: Calculate the total of the QueuedTime by using SUM(), and then use FILTER() to obtain the subset of the table for the Date and the QueueName needed.



=CALCULATE(
    SUM(CallDetail[QueuedTime]), 
    FILTER(CallDetail, CallDetail[Date] = ?) 
           && CallDetail[QueueName]= ?
            )
    )

Step 2: In the question mark area, we need to plug in a value/expression to restrict the rows been returned.


If we set they to the static value as “2012-02-18” and “Queue A”, the Sum of QueuedTime will be 900 for all the rows which won’t be correct for the Queue not equal to “Queue A” or the Date not equal to “2012-02-18”.


image

Step 3: To pass in correct expression, we could use the EARLIER() as the input for the certain value (i.e. Date or Queue name) that the calculation will be based on it. 


=CALCULATE(
    SUM(CallDetail[QueuedTime]), 
    FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date])
           && CallDetail[QueueName]= EARLIER(CallDetail[QueueName])
            )
    )

You can see the result now is correct based on the Date and QueueName

image

The EARLIER function is based on the current row context. If there is no row context, it will return an error.

Implementation Example:


Here is a scenario that I have encountered: Create a measure that contains the cumulated Queued Time only for the last queue.


image


To solve it, I add the following condition to the pervious formula based on the LastQueue flag



=IF(CallDetail[LastQueue] = 1, 
    CALCULATE(
        SUM(CallDetail[QueuedTime]), 
        FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date]) 
           && CallDetail[CallID]=EARLIER(CallDetail[CallID])
            )
        )
     ,BLANK()
    )

This way, only the last queue will have the total queued time in the column. image


When summing up in the Pivot table, the total of QueueTimeHandled per day will now match the total of QueuedTime.


image


My colleague Javier Guillen has another nice blog entry to discuss how to use the EARLIER() in DAX measure. You may want to check it out.


Reference:


http://technet.microsoft.com/en-us/library/ee634551.aspx

4 comments:

  1. Interesting read, thanks for sharing!

    ReplyDelete
  2. In this group there is hardly anyone who can know about this section i detail.so they are opening this new function here as quickly as possible.So they may start their work soon.





    Gaming Computer Cases

    ReplyDelete
  3. So beautiful and helpful article. I think this post composed of with some learning and acquiring lot of things.
    Desktop PCs

    ReplyDelete
  4. Thank you, this solved a similar problem of mine. Cheers!

    ReplyDelete