DAX is an awesome language and when paired with properly created relationships, DAX can add significant analytical value to your data models with minimum effort. But, you already knew that! So why are we here? Well, sometimes, DAX can produce results that are unexpected and this is usually very noticeable when looking at the totals row.
So why do we get unexpected results at the total row? Why doesn’t the total row simply return the sum of all the rows in a specified column? The simple answer is your DAX calculation is also computed for the total row and operates within the contexts of the total row. Let’s take a look at the previous screenshot.
In this example, the total row is returning all homes listed for all years in the filter context, this is in fact the sum of all the rows in the column. However, you may expect to only see the homes newly listed on the market for the latest time period, in this case that would be how many homes were listed in 2016. The result of 8,691 homes listed in the total row is not wrong or incorrect, it depends on what you are specifically looking for, it could definitely be unexpected depending on your analytical needs. If you do not wish to see the total of all homes ever listed in the current filter context then you have a couple of options available to you.
There are generally 3 ways you could address incorrect / unexpected totals.
- Pretend the problem doesn’t exist. (ProTip: Don’t ignore problems.)
- Identify when the DAX calculation is being evaluated for the total row and return a BLANK value.
- Identify when the DAX calculation is being evaluated for the total row and perform a different calculation.
In this blog post you are going to learn how to return blank to eliminate any confusion. In a future blog post you will learn how to use DAX to change the value of the total row.
HASONEVALUE function in DAX
In this simple example, the total row is the sum of all the rows, but this is not what we want. What we would want to show at the total row is how many homes have recently been listed for sale, not the total of all homes that have ever been listed. The first thing we must do is identify if we are at a total row. The way I do this is by using the function HASONEVALUE.
MSDN Definition:
HASONEVALUE: Returns TRUE when the context for the columnName has been filtered down to one distinct value only. Otherwise FALSE.
In the screenshot below I created a new measure called “Totals” and in this measure the function HASONEVALUE is used to correctly identify which row is the total row. This works because at the total level the filter context is all years, so the function returns FALSE.
The final step is to now use conditional logic and replace the Total row with a blank value.
BLANK() function in DAX
New Homes on Market (2)=
IF(
HASONEVALUE(‘Date'[Year]),
[New Homes on Market],
BLANK())
Now the total row is no longer confusing or misleading. In part two of this series I am going to take this a step further and show how to use DAX to return your expected results at the total row instead of just returning blank!
Further Reading: Rob Collie over at PowerPivotPro has a great blog post on HASONEVALUE vs. ISFILTERED vs. HASONEFILTER:
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
Thanks!
Mitchellsql 
Pingback: Unexpected Totals in DAX (Part 2) – Mitchellsql
Pingback: Unexpected Totals in DAX (Part 3) – Mitchellsql
Pingback: HASONEVALUE vs ISFILTERED in DAX – Mitchellsql
Hi Mitchell,
Thank you for explaining DAX stuff in plain and simple 🙂
I am trying to implement the method demonstrated here to identify whether I am at the total row. All I have is a single Customer (Id, CustomerName) table with two rows + a slicer on the CustomerName column. Everything works fine until I start to slice the table via the slicer. If I filter the table down to one row through the slicer, the HasOneValue functions shows True even at the Total row. If I check two values or erase the slicer completely, the HasOneValue starts showing False at the Total row as I need. Do you have any recommendations on how to identify the Total row when there is only one record in the dataset table or the table is filtered down to one record thru the slicer?
Thanks!
Hi Borka,
Working with total rows are quite challenging in DAX and the way you write your expression depends on what you are trying to achieve as well as how the data might be sliced. If you’re just trying to return blank, you might also look at the properties of the visualization and just remove the total row all together. But generally speaking, if you filter your report down to one customer id, then using HASONEVALUE on the total row will not work in this case.
Hi Mitchell,
Thanks a lot for answering, I really appreciate that!
You were right, I did not manage to make HASONEVALUE work in the situation described above. What finally helped me and solved the issue was the IsInScope function. Using it I was able to identify the total row no matter whether the table was filtered down to one row or not. And that is what I looked for.
Thanks again Mitchell, I am very pleased you made time and kindly answered me!
I have honestly never used isinscope, thank you for sharing your solution, I will certainly test this out later!
I just tried this out in a simple example and it works perfectly, the Microsoft explanation of this function makes no sense but I’m going to make a YouTube video of this example, thanks so much again for your solution. If you want other DAX Examples I do all my blogging on YouTube these days so feel free to check me out at http://www.youtube.com/c/MitchellPearson.