ALL returning different results in Excel and PBI

[pschommer]

Hi all,

I have a single table where I am trying to display a table of filtered data for one field and display mostly filtered data for another field. I intend to use the ALL function for the second field. I am seeing different results in Excel vs. PBI.

First field: Distinct Count of Visit

Second field (I want to remove the Lab Name filter from the count but include all other filters):

NewMeasure = CALCULATE(DISTINCTCOUNT(Table1[Visit]),ALL(Table1[Lab Name])) 

I expect field 1 to show different values depending on the Lab Name selected, but field 2 should remain the same regardless of which Lab Name is selected. I get exactly what I expect in Excel, but PBI gives me (mostly) the same result for both fields: the field 1 value.

If I select different lab names in Excel, the second column stays the same. This is expected. Doing the same thing in PBI makes the second column change.

I saw the post regarding the use of SORT and ALL returning different results in Excel vs. PBI, but there is no SORT in either of the examples.

Excel model can be found HERE.

PBIX file can be found HERE.

Any help on this vexing problem would be appreciated.

[Vvelarde]

@pschommer

Hi, you need to upload to drive, dropbox or similars and post the link.

[Vvelarde · Accepted solution]

hi @pschommer

You need to create the Year-Month Column in the Table.

Lab Date =
Table1[Lab Date and Time].[Year] & "-"
    & Table1[Lab Date and Time].[Month]

Use this in a Slicer and will Work.

[pschommer]

As you predicted, @Vvelarde, it worked! I would have NEVER thought of doing that. (Well, maybe not NEVER, but certainly not in the near future.)

PBI has the ability to create date hierarchies, and they're pretty slick. I like them and use them when appropriate. PowerPivot (in Excel 2013) does not have that ability, so I created my own date grouping of months. That was a difference between the two models that I didn't think much of. A date is a date is a date, right? Clearly, not so much.

To me, it seems odd that the way dates are grouped can cause PBI to return different results. There must be more to it that I don't understand.

(I believe I read something where Excel 2016 has date hierarchies in their models, but I'm using 2013).

Thank you so much for your response!