cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Need help with the syntax of Hasonefilter

I have a column total that is incorrect based on the row values. I beleive I need to use the Hasonefilter function but I can't seem to get the syntact correct. This my measure: Measure1 = sum('table1'[column1] * counta('table1'[column2])). Can someone please show me the syntax for the Hasonefilter function?

Thanks

1 ACCEPTED SOLUTION
Helper III

Unfortunately I wasn't able to get the HASONEFILTER function to work for me. I ended up using the  IF(COUNTROWS(values functions along with a Sumx function.

5 REPLIES 5
Community Support

Thank you for the quick response.

Hi @U156531 ,

I have something to add. I have created a simple sample, please refer to.

The [T&M] is a measure.

``````Measure = var _a = [T&M]
var _b = SUMMARIZE('Product','Product'[Product],"aaa",[T&M])
return
IF(HASONEFILTER('Product'[Product]),_a,SUMX(_b,[aaa]))``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

Unfortunately I wasn't able to get the HASONEFILTER function to work for me. I ended up using the  IF(COUNTROWS(values functions along with a Sumx function.

Super User

Hi @U156531

Have you checked official documentation?  HASONEFILTER function (DAX) - DAX | Microsoft Learn

This problem is hard to answer because you haven't provided any data and you state the column total is incorrect based on row values.  But not shown what is actually benig calculated.

Please provide sample data and show us what you think is wrong and what you want as a desired result.

I created some sample data.  The measure HOF is checking if one filter is applied.  As you can see there are no filters.

``HOF = IF(HASONEFILTER('DataTable'[Value]),1)``

If I use the slicer to show only values = 3

You'll see that HOF now returns 1 even for the Total row.

Note that my Measure is your measure code

``Measure = SUM('DataTable'[Value]) * COUNTA('DataTable'[Value])``

So for the Total row is is calcuating 9 * 3 = 27.

What exactly are you expecting in your data?

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Helper III

Phil, I'm tryimg to get value of the Mesaure in the Total row to equal the addition of the values in the rows. In you example above it should be 3 + 3 + 3 = 9.

Super User

Hi @U156531

That's what the Sum of Value column does, without needing to create a new measure?

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!