Measures affecting each other

[ph3062]

Short version

I have a measure which is giving a different result depending on whether it has been added to a table or matrix before or after another specific measure. Should this be possible? If so, what is it about these measures which is causing these results?

Details

My colleague noticed that he was getting different results in a table visualization depending on the order in which he added two measures to it.

I have tried to strip back the model and data in a sample pbix here:

https://1drv.ms/u/s!AlFWyUlAOrh8yXxbPz2FqS3YcOj6?e=NKxpOx 

The measures are defined as:

MEASURE CoverageMeasures[CustomerCalledCoverage] = 
var called = VALUES(DiaryCall[CustomerCode])
var dmGroupsCalled = SELECTCOLUMNS(CALCULATETABLE(Customers, TREATAS(called, Customers[CustomerCode]), Customers[DecisionMaker]=TRUE), "SubGroup", [SubGroup])
var groupCoverage = SELECTCOLUMNS(CALCULATETABLE(Customers, TREATAS(dmGroupsCalled, Customers[SubGroup])), "CustomerCode", [CustomerCode])
var totalCustomersCovered = DISTINCT(UNION(called, groupCoverage))
return COUNTROWS(totalCustomersCovered)

MEASURE CoverageMeasures[CustomerPlannedCoverage] = 
var planned = VALUES(DiaryAppointment[CustomerCode])
var dmGroupsPlanned = SELECTCOLUMNS(CALCULATETABLE(Customers, TREATAS(planned, Customers[CustomerCode]), Customers[DecisionMaker]=TRUE), "SubGroup", [SubGroup])
var groupPlannedCoverage = SELECTCOLUMNS(CALCULATETABLE(Customers, TREATAS(dmGroupsPlanned, Customers[SubGroup])), "CustomerCode", [CustomerCode])
var totalCustomersPlanned = DISTINCT(UNION(planned, groupPlannedCoverage))
return COUNTROWS(totalCustomersPlanned)

The idea is that Customers are organised into Subgroups. For each Subgroup, one Customer is designated as a “Decision Maker”. Customers may have been called (DiaryCall) or have a future appointment planned (DiaryAppointment). A call or appointment with a Decision Maker counts as covering all Customers in the same Subgroup. The intention of the measures is to give a total number of unique Customers covered by calls or planned appointments respectively – taking into account the calls to Decision Makers, but also making sure there is no double-counting if calls are also made to non-Decision Makers in the same group.

Query 1:

DEFINE

VAR subGroupFilter = TREATAS({"5LADU-1"}, 'Customers'[SubGroup])

VAR cycleFilter = TREATAS({"2021 Cycle 9"}, 'Calendar'[Cycle])
        
EVALUATE SUMMARIZECOLUMNS(
      'Customers'[CustomerCode],
      'Customers'[DecisionMaker], 
      subGroupFilter,
      cycleFilter,
      "CustomerPlannedCoverage", 'CoverageMeasures'[CustomerPlannedCoverage],
      "CustomerCalledCoverage", 'CoverageMeasures'[CustomerCalledCoverage]
    )

 

Query 2:

DEFINE

VAR subGroupFilter = TREATAS({"5LADU-1"}, 'Customers'[SubGroup])

VAR cycleFilter = ({"2021 Cycle 9"}, 'Calendar'[Cycle])
        
EVALUATE SUMMARIZECOLUMNS(
      'Customers'[CustomerCode], 
      'Customers'[DecisionMaker],  
      subGroupFilter,
      cycleFilter,
      "CustomerCalledCoverage", 'CoverageMeasures'[CustomerCalledCoverage],
      "CustomerPlannedCoverage", 'CoverageMeasures'[CustomerPlannedCoverage] 
    )

The two queries differ only by the order in which the measures are specified in SUMMARIZECOLUMNS, but give a different result with respect to the Called coverage for customer SUNNISTO37AAA. The result from the second query is correct - none of the customers have been called in the selected calendar cycle. I have included the Customer details in these queries to try to drill down on what is going on, but the same effect is seen at the total level.

If I change the CustomerCalledCoverage measure to simply return the ROWCOUNT of the called and groupCoverage variables then they are both empty as I expect in that specific example, but give one row as soon as we get their UNION, so it seems like that is where the issue is creeping in.

I could change the measures to test for the two empty lists as a special case, but I would much rather understand what is going on here. I had assumed (in my admittedly limited knowledge of DAX) that measures don’t affect each other, and it worries me that something like this might occur elsewhere without being spotted.

Any guidance is gratefully received.

[Anonymous]

It's very hard to diagnose such problems (this could very well be a bug as well). I'll tell you an even better story with SUMMARIZECOLUMS. We were building a query to test some theories we had about data at work (me and my peer) and we used the Contoso database hosted at https//dax.do. And we found that SUMMARIZECOLUMNS returned different numbers for the same measure depending on - hold on to your seat - whether or not we commented some other independent measure in the same query. Yes. It's totally unbelievable but it's true. We'll be reporting this issue to Microsoft.

Can you imagine that? You put 2 different measures in SUMMARIZECOLUMNS and the result  for one of them depends on whether or not you include/exclude the other one. SUMMARIZECOLUMNS is something I don't recommend using. Especially with filters in it. It's much safer to use it with CALCULATETABLE around it and the filters coming as arguments of CALCULATETABLE. At least then it seems to be much more predictable.

By the way, here's an article about the quirks (and there are many) of the function: https://www.linkedin.com/pulse/peculiar-behavior-summarizecolumns-dax-abhinav-khanduja/?trackingId=q...

[ph3062]

Thanks for taking the time to respond. This is... interesting. I think I'd rather someone told me my DAX was junk rather than it being a Power BI issue! The reason I quoted the queries with SUMMARIZECOLUMNS is because this is essentially the queries that the visuals are using, so I'm not sure if there is anything I can do about that?

What would be the best way for me to report this issue to Microsoft?

[Anonymous]

@ph3062 

You are very right. I also think that until MS have fixed the two issues we are talking about - yours and mine - one can easily say that DAX is junk. Which also means PBI is. The problem here is that it's very hard to see on datasets with millions or billions of rows that your measures/calculations are wrong; what's worse, the results will also depend on the distribution of data in your table/column! Yes, it's true. This is due to the 'auto-exist feature' that MS have introduced as an 'optimization technique.' Again, yes, they have deliberately chosen to make figures wrong "from time to time" 🤣 in order to keep speed (would anyone believe that?). They, I think, have made a deliberate decision to sacrifice accuracy for speed. And because very few people in the world actually check their figures against their datasets and find such issues... it's easy to pass such bugs over in silence. That's my opinion.

What's the best way to report it? AFAIR, they have a PBI website where you can log such things. You'll just have to google for it. Very soon I'll be logging mine as well. But I'm almost 100% sure MS are aware of this but have chosen to be silent and let people work with a tool that is fast, no doubts, but more often than we would like to think produces wrong numbers from correct measures/calculations. Shame... But what can we do? 😕