topic Filter Context - Importance of HASONEVALUE with VALUES function in DAX Commands and Tips

Filter Context - Importance of HASONEVALUE with VALUES function

Sania-F — Tue, 23 Sep 2025 09:34:13 GMT

Error - a table of multiple VALUES was supplied when single value was expected.

dax -

sales commission = [Revenue]* IF(VALUES('Sales Territory'[Country]) = "United States", 0.15,0.1)

DAX -

sales commission = [Revenue] *

if(HASONEVALUE('Sales Territory'[Country]),

IF(VALUES('Sales Territory'[Country]) = "United States", 0.15,0.1))
now, no error

Re: Filter Context - Importance of HASONEVALUE with VALUES function

FBergamaschi — Tue, 23 Sep 2025 09:50:06 GMT

VALUES('Sales Territory'[Country]) returns a table and you cannot compare a table to a value like "United States"

HASONEVALUE checks that VALUES returns a single value (a table of one row and one column) and in that case DAX turrns that table (of one row and one column) into a single value so it works

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Filter Context - Importance of HASONEVALUE with VALUES function

tayloramy — Tue, 23 Sep 2025 14:40:33 GMT

Hi Sania-F,

You’re seeing that error because VALUES('Sales Territory'[Country]) returns a table of distinct countries in the current filter context. When there’s more than one country in scope (totals, multi-select slicers, unfiltered visuals, etc.), comparing that table to the string "United States" makes DAX throw “a table of multiple values was supplied…”. HASONEVALUE() suppresses the error by short-circuiting when there isn’t exactly one country-but it also returns blank in those cases, which often gives wrong totals.

Use SELECTEDVALUE and aggregate per country with SUMX so totals add up:

Sales Commission := SUMX( VALUES('Sales Territory'[Country]), [Revenue] * IF( SELECTEDVALUE('Sales Territory'[Country]) = "United States", 0.15, 0.10 ) )

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

Re: Filter Context - Importance of HASONEVALUE with VALUES function

Anonymous — Wed, 24 Sep 2025 10:08:22 GMT

Hi Sania-F ,
I would also take a moment to thank FBergamaschi , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

Re: Filter Context - Importance of HASONEVALUE with VALUES function

Anonymous — Tue, 30 Sep 2025 05:01:05 GMT

Hi Sania-F ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Re: Filter Context - Importance of HASONEVALUE with VALUES function

Anonymous — Mon, 06 Oct 2025 05:54:56 GMT

Hi Sania-F ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions