VALUES function with HASONEVALUE and SUMX iterator function

Sania-F — Wed, 24 Sep 2025 11:00:57 GMT

sales commission iterator =

SUMX(

VALUES('Sales Territory'[Region]),

CALCULATE(

[Revenue]

* IF(

VALUES('Sales Territory'[Country]) = "United States",

0.15,

0.1

)

)
----------------------------------------------------------------------------------

sales commission = [Revenue] *

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

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

-----------------------------------------------------------------------------------

Iterator version = correct when multiple values exist, because it loops through each row and applies the right logic.
Simple version = works only when the filter context guarantees one country at a time. If multiple exist, it miscalculates.

In sales commission iterator, SUMX iterates through each region, evaluates [Revenue] * 0.1, then sums up.
In sales commission, you’re multiplying the total [Revenue] in context by 0.1.

Rule of thumb:

Use simple measure if your business rule applies a single rate per filter context.
Use iterator (SUMX) when you need row-by-row logic, e.g., different commission rates for different countries/products.

Re: VALUES function with HASONEVALUE and SUMX iterator function

FBergamaschi — Wed, 24 Sep 2025 14:41:59 GMT

This is a blog for asking for support, if you want to explain DAX you can consider other blogs

Re: VALUES function with HASONEVALUE and SUMX iterator function

v-sdhruv — Thu, 25 Sep 2025 05:17:22 GMT

Hi Sania-F ,
Thank you for sharing this. This kind of post is incredibly valuable to the community. Consider turning this into a blog post so others can benefit from your experience more easily
Power BI Community Blog - Microsoft Fabric Community

Thank You!

topic VALUES function with HASONEVALUE and SUMX iterator function in DAX Commands and Tips

VALUES function with HASONEVALUE and SUMX iterator function

Re: VALUES function with HASONEVALUE and SUMX iterator function

Re: VALUES function with HASONEVALUE and SUMX iterator function