Power Pivot Principles: The A to Z of DAX Functions – HASONEVALUE
12 March 2024
In our long-established Power Pivot Principles articles, we
continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at HASONEVALUE.
The HASONEVALUE function is one of the information functions. It returns TRUE when the context for columnName has been filtered down to one [1] distinct value only; otherwise it returns FALSE. It employs the following syntax:
HASONEVALUE (columnName)
This function has one parameter:
- columnName: this is required and represents the name of an existing column, using standard DAX syntax and it cannot be an expression.
The function returns a Boolean value:
- TRUE if there is exactly one unique value in the specified column in the current context
- FALSE if there are multiple values or no values in the specified column in the current context.
It should be noted that:
- an equivalent expression for HASONEVALUE function is
COUNTROWS(VALUES(columnNames))=1
- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Let’s consider the following example where we have the tbl_Example Table:
We will write the following DAX to check if the how to filter context affect the output:
DEFINE
VAR Filter_Out_A=CALCULATE(
HASONEVALUE(tbl_Example[Group]),
tbl_Example[Group]="A")
VAR Filter_Out_A_n_B= CALCULATE(
HASONEVALUE(tbl_Example[Group]),
tbl_Example[Group]="A"||tbl_Example[Group]="B")
VAR Filter_Out_A_n_C=CALCULATE(
HASONEVALUE(tbl_Example[Group]),
tbl_Example[Group]="A"||tbl_Example[Group]="C")
EVALUATE
{
("A Only",Filter_Out_A),
("A and B",Filter_Out_A_n_B),
("A and C",Filter_Out_A_n_C)
}
In the DEFINE statement, we construct the following variables:
- the first variable is filter out A in Group column
- the second variable is filter A and B in Group column
- the last variable is filter A and C in Group column.
In the EVALUATE statement, we output those variables. It will give us the following Table:
Since we filtered “A” only for the first row, the HASONEVALUE return TRUE. Whilst in the last row, we filtered for “A” and “C”, in the tbl_Example there is no “C” hence, “A” is the only unique value, so it returns TRUE for the last variable. For the second row, because we filtered for “A” and “B”, and “A” and “B” exist in tbl_Example, the output is FALSE.
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
