Problem with power pivot

[Lolo]

Hello,

I have an issue that I try to solve in power pivot, but I don't figure it out.
I know well Excel, power query is quite OK I would say , but I'm quite new in power pivot (However I have knowledge on SQL and on data models) .

here is my problem:
I have data that comes from my Oracle DB, and gives me like a schedule:
On a service, I have 1 vessel, that goes to a city then to another, etc... on a specific date.
When the continent change, I put a flag "Y" on the concerned location
(see file attached)

I would want to group for each service/vessel, all cities regarding the first previous city of the continent.

To test, and for the example, I have managed to do that in Excel, like in the file attached
* Source data are in Blue
* Requested calculated fields are in Green

Data are sorted by Service/vessel/date. And I have an array formula to identify the 1st previous city on the service/vessel that has the change flag = "Y". then I use a basic index formula to get the point and date.

But since I have a very big volume of data, it will not works in Excel (use of array formula...).
So I wanted to do the same in power pivot (or power query...)

1/ First, (for information) I have tried to do it in power query, (to summarize) by using function Table.Max in a function, and it works :

GetOpsData = (table as table, service as text, vessel as text, aDate as datetime) => Table.Max(Table.SelectRows(table, each [SERVICE_NO]=service and [VESSEL]=vessel and aDate>=[DATE] and [CHANGE]="Y"), "DATE")

By applying the function on each line, this give me a record for each line, and by expanding each record, I get the CITY and the DATE wanted. This way, for each line, I have the data of the 1st city of the continent where the vessel is passed just before.

But it only works also for a limited amount of data. When I put several thousand rows, the data refresh freezes, because of the function 'GetOpsData'
So impossible to use this solution.

2/ So I wanted to try with power pivot, since at the end I wanted to use power pivot to analyze data.

But:
* First I get data from ORACLE, with power query (because in reality I do some data transformation), and data are sorted by service/vessel/date, this is very important.
Pb when data are added to the data model and imported in power pivot, data are NOT sorted! And I don't manage to sort them in power pivot table by service/vessel/date

* Then even if it would be sorted, in all cases, I don't manage to use the PowePivot functions to create calculated fileds and do the same as in my excel or power query solution
I have tried CALCULATE() function with MAX function, FILTERS(), tried EARLIER() function too (but since data are not sorted as desired, I suppose it cannot work), tried TOPN also, nothing works.

Basically I just need to do in Power pivot an approximate VLOOKUP on the current table, and based on several criteria (like done in my power query solution :get [SERVICE_NO]=service and [VESSEL]=vessel and aDate>=[DATE] and [CHANGE]="Y")

I hope I'm clear and someone will be able to help me, because I'm stuck
Perhaps there is also another way to do what I want I don't know.

Thank you in advance for your valuable help.

 

[Lolo]

Hello,

just to say that I have found how to do it in power pivot. I'm learning, again and again

Not sure i was clear in my request, but anyway, I will try to quickly explain my solution it could helps, perhaps.

Problem:
I have a schedule table.
* A service is served by a vessel.
* A vessel go to several cities (points) at specific dates (eta_date).
* And I have a flag that indicates when the continent change (regarding the previous city)

I want for each service/vessel/city touched, get the previous point and date where the continent changed

----

First remark, no need to have data sorted in powerpivot.

1. create a column 'ClusterIndex': I need to sort by date, each row that have same service and same vessel
(please note that service and vessel are in other tables, that is why I use related function)
=RANKX (FILTER(ALL(DimSCHEDULE);RELATED(DimSERVICE[SERVICE_NO])=EARLIER(RELATED(DimSERVICE[SERVICE_NO]))&&RELATED(DimVESSEL[VESSEL])=EARLIER(RELATED(DimVESSEL[VESSEL])));DimSCHEDULE[ETA_DATE];;1;DENSE)

2. create a column 'OPS_TMP_IDX' : for each line I need to get the previous line where the continent has changed (for the same serivce and vessel)
=MAXX (FILTER(ALL(DimSCHEDULE);RELATED(DimSERVICE[SERVICE_NO])=EARLIER(RELATED(DimSERVICE[SERVICE_NO]))&&RELATED(DimVESSEL[VESSEL])=EARLIER(RELATED(DimVESSEL[VESSEL]))&&DimSCHEDULE[CALL_CONTINENT_CHANGE]="Y"&&DimSCHEDULE[ETA_DATE]<=EARLIER(DimSCHEDULE[ETA_DATE]));DimSCHEDULE[ClusteredIndex])

3. Create final column to get the related date based the OPT_TMP_IDX found.
=IF(ISBLANK(DimSCHEDULE[OPS_TMP_IDX]);DimSCHEDULE[ETA_DATE];CALCULATE(FIRSTNONBLANK(DimSCHEDULE[ETA_DATE];1);FILTER(ALL(DimSCHEDULE);DimSCHEDULE[ClusteredIndex]=EARLIER(DimSCHEDULE[OPS_TMP_IDX]))))

4. Do the same as point 3. but to get the point.

And it is quick, even with a lot of data


NB: If I have time I will send a sample file.