Linking tables with multiple parameters

[f4v5cs6oy3vis7j]

Hello wonderful, brainy people.

I've got a table covering multiple internal entities (e.g. A, B, C) along with some transaction data, including the position in the ledgers where this transaction appears (Entry No.).
It's worth noting at this point, Entity A will have an Entry No. of 1, and Entity B will also have an Entry No. of 1.

TABLE1

ENTITY	ENTRY NO.
A	1
A	17
A	84
A	105
B	2
B	17
B	53
C	1
C	5

I've got a second table which, again, lists all entities and consolidates the posting entries.

TABLE2

ENTITY	FROM ENTRY NO.	TO ENTRY NO.	APPROVER
A	1	25	Bob
A	26	84	Jim
A	85	120	Andi
B	1	13	Bob
B	14	48	Jeff
B	49	60	Bob
C	1	2	Jeff
C	3	8	Andi

I'm trying to find a way, for each row in TABLE1, to identify the approver of the range of those Entry Nos.
TABLE1 has over 10m rows; the database currently takes 1 hour to refresh, so a streamlined and elegant solution is desirable.

The output will look like this:

TABLE1

ENTITY	ENTRY NO.	APPROVER
A	1	Bob
A	17	Bob
A	84	Jim
A	105	Andi
B	2	Bob
B	17	Jeff
B	53	Bob
C	1	Jeff
C	5	Andi

Is this acheivable? I think it should be (if it wasn't for the entity, anyway), but I don't know if it'll be an M language step in the editor, a DAX language measure, or something else.
Any help or advice to resolve this would be greatly received.

[v-jianboli-msft · Accepted solution]

Hi @f4v5cs6oy3vis7j ,

Please try:

First create a custom column, here is the M code:

Table.SelectRows(Table2, (x)=>x[#"FROM ENTRY NO."]<=[#"ENTRY NO."] and x[#"TO ENTRY NO."]>=[#"ENTRY NO."] and x[ENTITY]=[ENTITY])[APPROVER]{0}

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@f4v5cs6oy3vis7j , A new DAX column in table 1

maxx(filter(Table2, Table2[Entity] = Table1[Entity] && Table2[FromEntity] <= Table1[EntityNO] && Table2[ToEntity] >= Table1[EntityNO]) , Table2[Approver])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[f4v5cs6oy3vis7j]

Hello @amitchandak ,

Thank you for trying.

In theory, yes, this should work. But as I mentioned at the start, I have over 10m lines.

I have applied the formula as you've laid out, but after 6 hours (and I've done this a few times over the course of the last few days), the columns are just taking too long to populate.

Can you suggest another solution to this task? New data will be refreshed every 2 hours and waiting an entire day for this to finish isn't practical.

Many thanks