One DAX Calculate formula gives different values in Pivot Table vs. Dataset view

[Anonymous]

Community,  I have never seen or thought this was possible - when I show my measure in Pivot table the measure calculates to count of 4203 but when I look at the same measure under my table in PowerPivot table view, the count shows 3610.  I tried Refresh of my TransactionTable but did not help; also checked if any fields in table view were filter (none).  Measure = Calculate(DistinctCount(TransactionTable[EmpID]),ISBLANK(Roster[EmpID])). TransactionTable lists over a million records of labor charges to various projects; Roster is RELATED (one-to-many) to TransactionTable on EmpID. I am calculating the number of unmatched records in Roster.  The community helped me get the measure to work (thanks), but I now want to get an answer to this question. Thanks. Jeff

[daxer-almighty]

Can you show some screenshots? It's not possible to diagnose this without the knowledge of the model.

[Anonymous]

Thanks for suggestion of adding ALL(Roster) at the end.  I tried this and the measure is still showing the 3610 instead of 4203 (on Pivot Table). 

[mahoneypat]

I haven't worked in PowerPivot for a while, but do the measures in the table view respond the the filters (for troubleshooting).  Do you have any filters in place in the table view in PowerPivot?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AntrikshSharma]

@Anonymous  When you say table view do you mean you have the measure in a column? if that's the case then context transition is making the difference in the calculations. Also would have to see data model to understand how to get you 4203, maybe try this in the column:

Measure =
CALCULATE (
    DISTINCTCOUNT ( TransactionTable[EmpID] ),
    ISBLANK ( Roster[EmpID] ),
    ALL ( Roster )
)

[Anonymous]

Responding to your question: when I say "table view" I mean in power pivot / management dataset / you see tables and when I click the tab for the table holding the measure, I see the measure BELOW the table rows and it shows 3601 count instead of 4203.  Jeff