Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
U156531
Helper III
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

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.

 

 

View solution in original post

5 REPLIES 5
v-rongtiep-msft
Community Support
Community Support

Hi @PhilipTreacy ,

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]))
 
vrongtiepmsft_0-1714012353056.png

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

 

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.

 

 

 

 

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.

 

 

PhilipTreacy
Super User
Super User

Hi @U156531 

 

Download example PBIX file

 

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)

 

hof1.png

If I use the slicer to show only values = 3

 

hof2.png

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

Hi @U156531 

 

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

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.