Unexpected Totals in DAX (Part 3)

At some point or another every DAX author realizes that the total row is not the sum of each row/cell in a given column. This can be quite confusing and is exactly why I am writing a third blog post dedicated to the total row.  If you missed part one or part two in this series you can find them below:

Part 1: Unexpected Totals in DAX (Part 1)

Part 2: Unexpected Totals in DAX (Part 2)

Problem

In this blog, we want to return YTD Sales for previous months and Forecast YTD Sales for the current month. Therefore, there should be only one measure and that measure should return YTD Sales or Forecast YTD based on the month.

In the following table there are three measures, the first measure is YTD Sales and it tracks the actual sales. The second measure is Forecast YTD Sales and this is the forecasted sales. The third measure is the Dynamic Measure, this is the proposed measure designed to replace the other two measures. However, the total row produces unexpected results, and therefore is perfect for this blog post:

SNAGHTML1168985d

In the above screenshot, the total row for the dynamic measure is displaying $19,582,120 but the sum of all the rows is actually $28,164,680. Let’s take a look at the dynamic measure and figure out why the total row is not what we might expect.

Dynamic Measure

The “Dynamic Measure” is returning the YTD Sales for completed months and returning the Forecast YTD Sales for the current month. Let’s take a quick look at the measure just to understand better what is going on. Please note, for this example we assume there are sales for every day of the year.

image

  • A = Two variables to make the code more readable (See descriptions below):
    • LastSaleDate = Return the last day the company had a sale (in the current filter context)
    • LastDayOfMonth = Return the last day from the date table (in the current filter context)
  • B = The logical test performed by the IF function.
    • If the two variables don’t match, then it is the current month and [Forecast YTD Sales] is returned.
    • If the two variables do match, then it is a completed month and [YTD Sales] is returned.

Filter Context of the Total Row

The total row is executing the dynamic measure within the context at the total row. What is the last day in the date table within the current filter context? At the total row it is 12/31/2008, there is a filter on the report page that filters the report down to only the year 2008. The last day that there was a valid sale was on 6/20/2008. Since 12/31/2008 does not equal 6/20/2008 the calculation returns Forecasted YTD Sales, which at the total level is $19,582,120.60.

Steps to solve this problem:

  1. Determine if the calculation is at the total row.
  2. For the total row perform the Dynamic Measure calculation for each month in the table separately.
  3. Sum the results of each result separately.

However, steps two and three above are a little bit more complex than they sound and therefore to complete the solution we need to introduce you to two new functions in the DAX language: VALUES and SUMX. The great news is this is a design pattern that you will be able to use in many different scenarios!

The VALUES function in DAX

MSDN Definition:
Returns a one-column table that contains the distinct values from the specified table or column.

Remember step 2 from above? We want to execute our dynamic measure against each row in the table, the VALUES function will get us the distinct list of the months. Let’s take a look at the VALUES function in action. I have created a table using the following formula to display the results:

SNAGHTML11aaa388

image

The VALUES function returned a distinct list of months. This is exactly what we need. Now, how do we execute our measure against each row in this table and then SUM all the results at the very end to get the expected value at the total row? SUMX!!

Working with the SUMX Function in DAX

MSDN Definition: Returns the sum of an expression evaluated for each row in a table.

Syntax:

image

Perfect! The SUMX function is going to iterate over the list of months and execute the dynamic measure against each month. After this process has completed the SUMX function will then SUM the results returned for each month.

SUMX accepts two parameters, the first parameter is a table and the second parameter is the expression. The table that is returned from the VALUES function is the first parameter and the dynamic measure is passed in as the second parameter (expression). Let’s take a look at the final solution.

Solution

The following solution now returns the expected results for the total row:

image

Here are the results, pay special attention to the total row:

image

Thanks for reading my post!

Power BI–Problem, Design, Solution (Text Search)

 

There is a video that walks you through the solution in depth, please find the video at the end of this post along with the completed solution.

In this installment of the Problem, Design, Solution series we are going to show you how to perform a text search using slicers in Power BI, this simulates a “LIKE” type search. In the following screenshot you can see that when “Tax” is selected all records in the table that have “Tax” anywhere in the record are returned, likewise whenever “IT” is selected from the slicer all records in the table that have IT in them are returned. Hope you enjoy this post!

Solution_LikeSearch

Problem

Normally slicers (visual filters) only filter records that are an exact match, for example, if you select “IT” from the slicer then you would only expect to see records that have IT and only IT in the record, if it does not match IT exactly then those records are not returned. This is the intended behavior of a slicer and in general, this is exactly what we want. However, there are times when you might want to do a “LIKE” type search similar to the like function in T-SQL. In this scenario, we had a client who needed to do this exact thing. The screenshot below is our simulated problem for this example.

In order to bring back all records that contain “IT” you are required to click each occurrence of “IT” in your slicer, of course, this isn’t a big problem if you have a small number of possible combinations like our sample size. In larger environments, this may be an unreasonable expectation!

Problem_LikeSearch

Design

Now that we have discussed both the problem and what we want the ultimate solution to look like, it is time to lay out the design. The design includes a four step process.

Let’s break this down a little bit:

  • The first step in the process is to identify all the unique values that we need to filter on and put those values into their own distinct table, in this case, we are looking at departments (HR, IT, FIN, Audit, and Tax).
  • The second step is to create a measure that uses the distinct departments in our new table as a filter. We will call this measure IsFiltered.
  • The third step is to create a new slicer using the values listed in our disconnected table.
  • The final step is to tie our new measure (IsFiltered) to the specific visual that we want to be affected by our disconnected table of distinct values.

Step 1 – Loading unique values in a distinct table

I would recommend storing this list of values in a file or in a table in a database, for this example I will quickly put these values into a table in my PBIX file. Navigate to the home ribbon and select “Enter Data”, I will name the column Departments and the table Slicer, next I will add the five distinct departments as records for the departments’ column (IT, HR, Tax, FIN, Audit).

Note: This is using the disconnected table design, we are not going to set up a relationship between this new table and any other table in our model.

Step 1_LikeSearch

Step 2 – Creating the IsFiltered measure

This IsFiltered measure is going to give us the “LIKE” type search that we are looking for! Excited yet?? Well, you should be! In this example I am going to use the FIND function, the find function is exactly like the SEARCH function with the key difference being that the FIND function is case-sensitive and the SEARCH function is case-insensitive. The code below will complete step 2 in our design.

IsFiltered =
IF(
SUMX(‘Slicer’,
FIND(
‘Slicer'[Departments],
MAX(‘Department Goals'[Affected Departments]),,0)) > 0,
True“,
False
)

What is this measure doing? The FIND function returns the starting position of the text string we pass in, if the value is greater than 0 then that means that the text exists and this is how we generate a LIKE type search. If the text is not found then nothing is returned and we give that record a default value of 0.

Step 3 – Create a slicer from the disconnected table

Now we can create a slicer from our distinct list of values. At this point the slicer does not filter anything, this is because the filtering occurs through the measure we created in step 2 and needs to be added as a filter to a visual or report in our Power BI Report.

image

Step 4 – Use the IsFiltered measure to filter a visual in the report

The last step in this process is about as easy as step 3, first we need to figure out which visuals we want to be filtered with the “LIKE” type operation and then we need to use the IsFiltered measure as a filter on those visuals. The criteria here will be to only show the records where the result is “True”. Remember the DAX calculation from above? If a value was found, then a number higher than 0 is returned and we flag the record as “True”!

Finally, I am going to add the IsFiltered measure as a filter to my table visualization, this is done in the Fields section of the visualizations pane. See the below image:

Step 2_LikeSearch

 

Solution

With the 4 step design completed, the solution is also complete! Now we can apply a filter that does a text search rather than an exact match, see the image below:

Solution_LikeSearch

Summary

By leveraging the DAX expression language we are able to override the default behavior of slicers and filters in Power BI. Please note that this solution would also work in an SSAS Tabular model, the implementation would be slightly altered as you may be consuming that model from excel rather than Power BI but it would work nonetheless!

Resources

Video of the solution:

Completed Example:

http://tinyurl.com/yd2qon7x