cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
twintrbl Helper I
Helper I

Using IF(HASONEVALUE() returning incorrect (and SLOW) results

Hi Folks,

 

I'm aware of the odd behavior of totals with measures, so I created a measure with the IF ( HASONEVALUE ()... function to differentiate between the values for the rows in a table visual versus the grand total. As long as I write the measure so that it returns a BLANK() when HASONEVALUE is false, it works great (10ms or so). But if I change the BLANK() to 1, it suddenly crawls (85,000 ms). I'm using DAX Studio to troubleshoot this, and it shows me that once I give it something to display in the case where IF ( HASONEVALUE ()... is false, it seems to forget that the table visual is filtered and wants to return a row for every value in the whole freakin' underlying table. And if I actually give it a calculation to perform for this total row, it times out and won't display the table visual at all. In DAX Studio, I tried it and gave up waiting for it to complete after about 10 minutes.

 

So.... here is something a little more concrete. Cut down to its basics, I have a table visual showing Item Number, Model Number, and Average Billed for that Model. This entire page has been filtered based on a Drillthrough to a single customer Agreement. So there's only one customer agreement on the page, but there could be anywhere from 1 to thousands of items on that agreement. Each item has a specific model. (They could have multiple items that are the same model.) And each model will have a calculated average billed amount. For simplicity's sake, we'll say that all this data is in two underlying tables: Table 1 is "Agreement Items":  it contains every item in every agreement (with the related metrics like model id, billed amount, etc...). The other table "Models" is a summarized table that contains one row per model number per Agreement with the totals for those metrics (Total Billed and a Count of items).  (To be totally precise, there is actually a "middle" table in here too that only has a single value for each model to avoid a many-to-many join between the Agreement Items and Models tables.)

 

The table visual shows the each Item Number from the Agreement Items table (pre-filtered to only show items from a single Agreement), the Model from the Agreement Items table, and a measure for the Average Billed for that Model (a sum of the total billed divided by a count of the items). The measure as it stands is defined as:

 

=IF ( HASONEVALUE ( 'Agreement Items'[Model]), SUM ( 'Models'[Price] ) / SUM ( 'Models'[Count] ), BLANK() )

 

This runs very fast, with each of the rows showing exactly what they should.  In DAX Studio, copying the underlying query for the visual and running it there returns a table of values that looks exactly the same as the one in Power BI.

 

Now, if I change that measure to be

 

=IF ( HASONEVALUE ( 'Agreement Items'[Model]), SUM ( 'Models'[Price] ) / SUM ( 'Models'[Count] ), 1 )

 

It takes forever to run, and when it returns the results in DAX Studio, it doesn't just return the items/models from that one customer. It returns a table with every item number and every model in the Agreement Items table, with each of the items that are NOT in this particular customer's Agreement showing the "result if false" of a 1.

 

So - all I can figure is that for some reason, the IF ( HASONEVALUE () function is not respecting the page filter and is forcing it to return every value in the Agreement Items table in the "result if false" section. 

 

I've run into similar behavior whenever I use an "IF" in a measure - where it suddenly wants to return values for rows that should have been filtered out.

 

FWIW, I've tried using HASONEFILTER and ISFILTERED instead of HASONEVALUE, and those return no results at all - no rows.

 

Any ideas what I'm doing wrong? I'm sure it's some kind of data modeling issue with my underlying tables, but I'm at a total loss for why the "result if false" part of the IF statement is ignoring the fact that the table/results are already filtered, much less what to do to fix it.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Using IF(HASONEVALUE() returning incorrect (and SLOW) results

Hi  twintrbl,

I am not clear about y0ur data structure and your design, so I am not sure which factor cause this problem. If possible, could you please share some sample to us? In addition, you also could refer to DAX – HASONEVALUE vs. ISFILTERED vs. HASONEFILTER , Improve Power BI Performance by Optimizing DAX  and Strange behavior of HASONEVALUE and SELECTEDVALUE  for details.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

View solution in original post

1 REPLY 1
Community Support
Community Support

Re: Using IF(HASONEVALUE() returning incorrect (and SLOW) results

Hi  twintrbl,

I am not clear about y0ur data structure and your design, so I am not sure which factor cause this problem. If possible, could you please share some sample to us? In addition, you also could refer to DAX – HASONEVALUE vs. ISFILTERED vs. HASONEFILTER , Improve Power BI Performance by Optimizing DAX  and Strange behavior of HASONEVALUE and SELECTEDVALUE  for details.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors