I use the function HASONEVALUE quite often, many times it is in the context of solving problems with the total row. I wrote a blog on how to handle totals using HASONEVALUE here:
Unfortunately, HASONEVALUE is not always the tool for the job. For this scenario I am returning Homes Sold Month-to-Date. The final solution should look like the following screenshot:
As you can see in the previous image we want to return the Homes Sold MTD if we are at the day level or the month level, but if we are at a level higher than the month level then we simply want to return a blank value. It doesn’t make sense in this scenario to show MTD homes sold at the quarter or year level. If we use HASONEVALUE(‘date’[Date]) here, then no value is returned at the Month level. Here is an example:
As you can see in this example, a blank value is returned for the month of January. As previously mentioned the desired behavior is to return a value for the month of January.
IsFiltered
IsFiltered: Returns TRUE when columnName is being filtered directly. Else, False.
Let’s take a look at what IsFiltered returns if we are checking to see if the date is filtered:
ISFILTERED returns False for the month and Year as expected. Now, we can simply chain multiple IsFiltered functions together to return our desired results. In the following screenshot I added an or condition to check if the month was also filtered. As you can see, the results now return TRUE at the day and month level.
Now we can apply some conditional logic using the IF function based on the results of the IsFiltered expression in example above. If the day or month IsFiltered then return the MTD homes sold, else return BLANK(). See below:
Thanks for checking out my blog!