# 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**.*