How to use a local table to exclude values in a remote table?

[C4YNelis]

Hi all,

what I'm trying to achieve is to use a live dataset in a mixed mode report where I have an imported table (Excel file), where users want to have the option of manually excluding certain products for a given period (year) from the report. I've been struggling with this issue for far too long now, so hopefully someone will either confirm this is not possible the way I see it, or point me in the right direction.

The remote dataset has a model that uses both an orderheader and orderrows table which  have a relationship together filtering both ways. The orderheader table knows the year of the order, the orderrows table knows the product, which both correspond to the same columns respectively in the third (local) table: exclusions.

So my first question: I know it is not supposed to be possible to push data from the local side to the remote side, however, is it not possible to get data from the remote tables to filter the local table either?

And for my second question: there are examples where you could use relationships to filter a remote table by a local table. However, I need the opposite (that is, keep everything remote that is NOT in the local table). (How) can I achieve this?

I hope the image is somewhat clear, it's not my best drawing.

Two remote tables, one local table

Thank you for your consideration,

Cheers,

Niels

[C4YNelis]

Hi @Greg_Deckler ,

I'm sorry for my Friday afternoon post. Looking back, it may have been a bit rushed indeed. Great post in your link btw, I think I came across it a few years back already. And while I'm at it, may I suggest that with the recent arrival of native Calculation Groups within the GUI of PBI Desktop itself, it might be interesting to link to the calculationgroup introduction of SQLBI, which imho is also a great read: Introducing Calculation Groups - SQLBI. In the context of Time Intelligence, Currency and more, quote interesting.

However, as far as I can see, my specific problem is not yet listed there, so let me elaborate:

What I have is an existing model which I include as a live dataset. The blue tables are the remote tables. I've previously supplemented this report with a few local tables, which gave me my desired report.

However, the case has changed somewhat, where certain products in certain periods now need to be excluded. The white table "Uitzonderingen" (Exceptions in Dutch), is my new local table, which contains a table of products per year that should be excluded from the report.

Part of the model

Don't pay too much attention to the relationships in the model btw, I've been playing around with them and tried several different setups, where so far, having one active relationship seems to work best. In the printscreen they are all inactive, there to be activated by crossfilter on demand in the measure.

Below is some sample data. The Orderheaders and Orderlines are remote, Exceptions is an importtable:

So indeed, the first thing I tried (without having any relationships between the local and the remote tables), was to use TREATAS and IN, just like you suggested. However, this resulted in an endless wait for the visual to show me something.

What does give me a result (albeit the opposite as desired) is when I activate the relationship between the Exceptions table and the orderlines on a productlevel:

isExcluded =

VAR someYear = VALUES(Orderkoppen[facturatie jaar])

RETURN calculate(countrows(filter(Uitzonderingen, Uitzonderingen[Jaar]=someYear)))

In this case it will return to me those lines that are present in the exceptions table (both counting the number of orderlines). However, when I turn it around and for instance test with:

isExcluded =

VAR someYear = values(Orderkoppen[facturatie jaar])

VAR noOfLines = calculate(countrows(filter(Uitzonderingen, Uitzonderingen[Jaar]=someYear)))

RETURN noOfLines > 0

it will run endlessly and never return the visual. The same happens when instead of COUNTROWS I use ISEMPTY.

When I try to run without active relationships, I get the same behaviour, regardless of the crossfiltering settings, or whether I use TREATAS.

So my plan B is to load a very minimal copy of the orderlines (products, years and orderline_key) and use that to INclude everything in a simular way setup. However, this feels like a very dirty and inefficient workaround.

Hopefully this makes it any clearer. Any thought on how I might manage this in a better way?

Cheers,

Niels

[Greg_Deckler]

@C4YNelis You can use TREATAS or IN for cases like this. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

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