Inconsistent Measure Responses

[pjmorris]

I have two measures which give different results, yet as far as I can see they are fundamentally identical. Grateful for any thoughts. (Note, although the code snippets say PowerQuery they're actually DAX measures in PowerPivot)

The first measure returns 3325 and is defined as:

CountApproved=CALCULATE(DISTINCTCOUNT(qryVault[Babcock Doc No]), qryDDM,
    ALL(qryDDM[RACI]),  qryDDM[RACI]="A",
    qryVault[Issued Date]>0,
    qryVault[Status]<>"Superceded",
    qryVault[Status]<>"Obsolete"
)

The second returns 3294 and is defined as:

CountRACIisA=CALCULATE([CountAllIssued], ALL(qryDDM[RACI]), qryDDM[RACI]="A")

with [CountAllIssued] defined as:

CountAllIssued=CALCULATE(DISTINCTCOUNT(qryVault[Babcock Doc No]), qryDDM,
    qryVault[Issued Date]>0,
    qryVault[Status]<>"Superceded",
    qryVault[Status]<>"Obsolete"
)

My reading of these measures is that fundamentally the CountApproved and CountRACIisA are identical. However, if I remove qryDDM[RACI]="A" from [CountApproved] it makes no difference , whereas if I remove it from [CountRACIisA] the result is 3325. Which suggests that the qryDDM[RACI]="A" is not being considered in the filter context of [CountApproved].

I'm confused.

Regards

 

[GraH]

Maybe there is a way to simulate how the measures are calculated by the engine.
For the combined measures, you actually have a calculate inside a calculate, thus you remove filter context twice and create a new filter. The first measures is a single calculate, and the new filter is defined using all. The way these are calculated by the engine is very different. Now I'm not at that level I can immediately explain the difference in the result without seeing data.

I remember I saw a video on a similar issue on the website of the Italians Ferrari and Russo Home - SQLBI. Maybe you find some answers over there as well. Sorry, I don't recall exactly the name of the video. It did feature all and calculate in measures that look the same, but return different results.