oData Filter Question

[Promethes_2]

Hello,

I'm trying to solve the following issue. I have 2 entities, Sales and SalesLines.  Due to the size of the SalesLines entity, I need to filter SalesLines to only the last 90 days of transactions (otherwise the Power BI service fails to refresh the entity data).  However the SalesLines entity does not have a date field, but is related to the Sales entity by TransactionNumber [Sales[TransactionNumber] -> SalesLine[TransactionNumber] )1 to Many).

I filter the Sales entity by transaction date like this (which works):

= OData.Feed("https://MyDataFeed.com/data/Sales?$filter=TransactionDate ge  2017-06-01"

But I can't seem to determine how then to dynanically filter the SalesLines to only the transactionnumbers in the sales entity.  The odata doesn't have an IN function.  Is there a way to accomplish this?

This is basically what I'm after but not certain if it's possible?

= OData.Feed("https://MyDataFeed.com/data/SalesLines?$filter=TransactionDate IN Sales[TransactionNumber]"

[Greg_Deckler]

Could you bring in all of the SalesLines but do a Merge with your Sales table on TransactionNumber, discarding any unmatched TransactionNumbers?

[Promethes_2]

Unfortunately no becuse the Power BI service won't bring in the entire SalesLine entity.  It fails so my thought for a work around was to filter down from the odata url the total data coming into Power BI.

[Greg_Deckler]

Why not:

= OData.Feed("https://MyDataFeed.com/data/SalesLine?$filter=TransactionDate ge  2017-06-01"

?

[Promethes_2]

Because SalesLines does not contain a transaction date.  It is related to the Sales table by Transaction Number.  Basically the Sales table is the header table and the SalesLines table is the lines related to the Sales table.

Example:

Sales Entity: TransactionNumber 1, TransactionDate 2017-01-01

SalesLines: TransactionNumber 1, Line 1, TransactionNumber 1, Line 2, TransactionNumber 1, Line 3.

I can filter Sales entity but need the related TransactionNumbers from SalesLines.

[Greg_Deckler]

Ah, your original post was misleading me. That second OData link should have been TransactionNumber IN... instead of TransactionDate IN... Got it.

Are your TransactionNumbers sequential? Could you potentially grab the min and feed that into the filter?

[Promethes_2]

Oops, typo on my part, apologies.  Unfortunately the TransactionNumbers are random so to speak so I can't do that.  It really doesn't seem like I can accomplish this but wanted thoughts before I call it quits on this strategy.