Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
f4v5cs6oy3vis7j
Regular Visitor

Linking tables with multiple parameters

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

ENTITYENTRY NO.
A1
A17
A84
A105
B2
B17
B53
C1
C5

 

 

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

TABLE2

ENTITYFROM ENTRY NO.TO ENTRY NO.APPROVER
A125Bob
A2684Jim
A85120Andi
B113Bob
B1448Jeff
B4960Bob
C12Jeff
C38Andi

 

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

ENTITYENTRY NO.APPROVER
A1Bob
A17Bob
A84Jim
A105Andi
B2Bob
B17Jeff
B53Bob
C1Jeff
C5Andi

 

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.

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1672305853669.png

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.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1672305853669.png

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
Super User
Super User

@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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.