Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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.
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.
@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
User | Count |
---|---|
89 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |