Advanced Filtering "Is not"

[Fred14]

If we consider a dummy short ist of Company names called from “A” to “Z” included, it appears that adding an Advanced filtering criteria (on a simple table) like “Company <> A” is not translated on generated (Oracle) DirectQuery SQL Statement as “Company.Name <> A” but as “Company.Name in (B, C, D, E, F, G, H… X, Y, Z) or Company.Name is not null”…

Such kind of query is no way efficient (in real-life with thousands/millions of records table). How to change the "PBI behaviour" to generate “Company.Name <> A” as SQL Statement.

Thanks in advanced for your guidance.

[amitchandak]

@Fred14 , Try like

Measure =
CALCULATE(COUNTX(filter(Company,Company[Company] in EXCEPT(ALL(Company[Company]),filter('Company',[Company]="A"))),[Company]))

[Fred14]

Thanks for your prompt input, amitchandak, much appreciated...

The problem in my case is that I promote a Dataset to end users not to let them build complex code. "Just" for self-service, simply by drag and dropping available objects.

The case of an inappropriate generated SQL can be reproduced for Company Name as explained on my example but also for plenty other Dimensions of my snowflake datamodel.

The question is why PBI does not generate a SQL Statement that excludes exceptions, as requested/implemented on the filter panel but instead, generates a SQL Statement that want to include all the other (good) values (than the exceptions)... Imagine how long and inefficient is the query when the end user want to exclude 2 out of 1500 Names: the generated SQL lists 1498 names to be matched instead of 2 to be excluded.

Is there a way/setting to indicate PBI to build the accurate SQL Statement?

[v-deddai1-msft]

Hi @Fred14 ,

Based on my test, It behaves as I expected, the converted SQL query is also <>. Not what you described. How did you apply filter <>A ?

Here is the screenshot for my test (<>1):

Best Regards,

Dedmon Dai

[Greg_Deckler]

Sorry, I did not follow this at all. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Fred14]

Thanks for your input but sorry not being clear enough, let me try again.

Through Desktop app, I've noticed low performance on a refresh of a simple list supposed to display a distribution of Incidents (from Fact Table "Incident") per "Company" Names (Dimension Table). 

Fact Table "Incident" is joined to Dimension Table "Company" through many-to-one active relationship, Incident.company_fk=Company.PK...

The displayed count consists in excluding "Company" Name A, so on the concerned visual, the advanced Filtering clause is implemented as "Company Name <> A".

Through Performance Analyzer, by looking at the generated SQL Statement, I would have expected having something like "Select Company.Name, count(Incident.Ref_Num) from Incident, Company where Incident.company_fk = Company.PK and Company.Name <> 'A' group by Company.Name".

Instead of this, the generated SQL is "Select Company.Name, count(Incident.Ref_Num) from Incident, Company where Incident.company_fk = Company.PK and (Company.Name in ('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', ...,  'X', 'Y', 'Z', ) or Name is not null) group by Company.Name"...

Such generated where clause is not accurate and has low level of performance.

The above example with a dummy table of 26 Company.Names is just an illustration of an encountered "real-life" case where my end-users would want to obtain the distribution of millions of "Incidents" per Companies names, by simply excluding few Company names out of the Dimensional Company table tcurrently hat contains thousands of records. Following the above example, the generated SQL is enormous and performance "normally" bad... Instead of excluding few exceptions as requested, the SQL is built to include all the rest...

Hope it makes more sense.

Regards.