Filter Context - Importance of HASONEVALUE with VALUES function

[Sania-F]

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

[tayloramy · Accepted solution]

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.

[v-sshirivolu]

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

[v-sshirivolu]

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.

[v-sshirivolu]

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

[FBergamaschi]

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