FILTER to work differently in DAX Studio / PowerBI?

[Anonymous]

Dear all,

yesterday I managed to make a quite complex code in DAX Studio (at least for my expertise level) and was very happy to be working.

After transfering to PowerBI measure it worked differently, however. Here my minimum/anonymized example:

Background: I'm trying to summarize the different LOTs used in a given production run. I've got a table for the production main data plus another related for the compounds (and batches) used in that. This second table can be viewed as:

[PkID]             [CompoundName]     [Batch]

123                  CompName1             A
123                  CompName2             B

123                  CompName3             C

124                  CompName1             A
124                  CompName2             D

124                  CompName3             E

Targeted result: I'd like to have a table filtered to only those compounds that did change from previous to current prod. run

Solution path (so far): I used "Summarize" to group the above example table by CompName and calculate the distinctcount of batches.

VAR content = <sets the filter context of current and previous production run>

VAR output = FILTER(
                                    SUMMARIZE(CALCULATETABLE(Table2,content,ALL(Table2)),[CompName],
                                    "cnt",DISTINCTCOUNT(Table2[Batch])),
                                    [cnt] > 1)

Current Issue: The result is fine in DAX Studio. It returns a table with only rows for CompName1 and CompName2 (excluding CompName3, as its distinct batch count = 1). However, transferring this to PowerBI  I converted that table just to "COUNTROWS(output)" to retrieve a scalar, but I am getting a 3 whatever I do... no error message...

In DAX Studio it is all fine - but why isn't the FILTER not working in PowerBI?

[daxer-almighty]

@Anonymous 

Your problem must be reproducible on my end. Then and only then will I be able to do anything about it.... Without any concrete data I can't do much.

[daxer-almighty]

First and foremost, you should never use SUMMARIZE to do anything else in it but grouping rows. No calulations should be performed. If you want to know why, you can get familiar with this: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Secondly, with the amount of info you've shown it's hardly possible to diagnose any problems...

[Anonymous]

Good morning, and thanks!

I know that article - and tried, but never achieved the results I expected to have otherwise.

Using addcolumns(....) gave far higher distinct counts, giving reason to believe that the filter context (at least in my code), was different when doing the calculation outside the summarize...

It is also my own problem, that there is no more info to go with, as I said - I don't get error messages I just get different results from DAX studio and PowerBI. What would it be you're missing as info?