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.
