🎵 On the sixth day of Christmas, my true love gave to me...
Verified User Selections,
and
Verify User Selections with DAX Information Functions
There are a few DAX functions that don't really perform any calculation on your data model, but instead simply tell you something about the current state of your data model, including row and filter context. These functions can be really helpful in verifying that your report user has done what you want or need them to, before allowing them to see the next page, number or calculation.
Today we are going to see how the HASONEVALUE function can help verify that a user has made a single selection in a slicer, which is a common requirement in many business scenarios. In our example, we need to verify that the user has only selected one name before we can check Santa's list to see if that name is on the naughty or nice list. We will use the HASONEVALUE function to verify that they have only selected one name, and then change the button text or prompt when they have selected exactly one name.
DAX Function: HASONEVALUE()
HASONEVALUE is not a vital DAX function. It can be replaced with the True/False expression:
COUNTROWS(VALUES(<column>))=1
That is exactly what HASONEVALUE() does, it simply checks if there is exactly 1 row in the column. HASONEVALUE is a very simple function that takes only one argument:
- Column Name (required)
The function returns "True" if there is only one row in the column or "False" if there is more or less than one row in the column.
Okay, so we have lots of columns in our data, and in pretty much any dataset they will all have more than one row, so what good does this function do? It tells us information about the current filter context of the report. For those of you who know a bit about DAX, you'll know that Context is very important. For those of you who are new to DAX, the HASONEVALUE function let's us see what our data model looks like after it has been filtered, sliced and diced by all the report visualizations and interactions from the end user.
In the table above we have used the HASONEVALUE function on the 'Santa List'[Name] column:
Name HASONEVALUE =
HASONEVALUE ( 'Santa List'[Name] )
We have put this in a table visualization with the 'Santa List'[Name] column. You can see that when the table provides a filter context of one name (aka one row), the HASONEVALUE function returns "True", but when we get to the Total row and no longer have that filter context, the HASONEVALUE function returns "False".
We will get the same result if we put this in a card visual with a slicer for 'Santa List'[Name]:
This is pretty cool, but not exactly helpful in creating a beautiful report, so information functions such as HASONEVALUE are often used in conjunction with other functions to add functionality. Today, we will use a tried and true IF() function to change the text based on the result of the HASONEVALUE function.
Button Text =
IF (
HASONEVALUE ( 'Santa List'[Name] ),
"Click to see if " & SELECTEDVALUE ( 'Santa List'[Name] ) & " has been naughty or nice",
"Click to see Santa's entire list"
)
Tip: Information functions that return "True" or "False" are a great partner for functions that require a 'logical test' as one of their arguments.
You may have noticed that HASONEVALUE returns "False" if the column has been filtered for more than one name. In our button text above, we have written "Click to see Santa's entire list". This should make it clear to the user that their filter selections won't carry through to the next page. If this does not meet your requirements, you might find the ISFILTERED function to be helpful too.
How to:
- Open the SantaList.pbix file.
- Create the Button Text measure from above.
- (Optional): Test it out using a table or card visual, or if you're feeling brave try a button but we'll learn how to make those tomorrow.
- Save the report.
That's all you need to do for today. Tune in tomorrow for the next gift in the 12 Days of Christmas series when we'll put this measure to work as a page navigation button.
