topic Re: FILTER to work differently in DAX Studio / PowerBI? in DAX Commands and Tips

FILTER to work differently in DAX Studio / PowerBI?

Anonymous — Wed, 14 Jul 2021 11:39:48 GMT

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?

Re: FILTER to work differently in DAX Studio / PowerBI?

daxer-almighty — Wed, 14 Jul 2021 14:59:22 GMT

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...

Re: FILTER to work differently in DAX Studio / PowerBI?

Anonymous — Thu, 15 Jul 2021 04:32:20 GMT

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?

Re: FILTER to work differently in DAX Studio / PowerBI?

daxer-almighty — Thu, 15 Jul 2021 20:14:13 GMT

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.