Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have two tables. Project table and debtors table. Both tables have matching unique transaction ID's. I'm looking to do some sort of vlookup to populate a project number on my debtors table.
Here's what I'm looking to achieve:
Project Table Debtors Table
Trans ID Project # Trans ID Project #
1 20-24050 1 Blank
2 20-24050 2 Blank
I want to populate a project # on my debtors table based on the matching trans ID's from each table.
Hope this makes sense.
Thanks!
Solved! Go to Solution.
@Andrewutter12 , an error arises when more than one result correspond to Debtors[Tranns ID New]. You may try this formula in a calculated column,
Column =
MAXX (
FILTER (
z_ISG_InFocusProjectQuery,
z_ISG_InFocusProjectQuery[Trans ID New] = Debtors[Tranns ID New]
),
z_ISG_InFocusProjectQuery[ProjectNumber]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
You can do this in PQ (probably better) or in DAX. In DAX the code for the Project# column in the debtors table would be:
Project# =
LOOKUPVALUE ( ProjectT[Project#], ProjectT[TransID], DebtorsT[TransID] )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hello,
This is the error message I'm receiving when using that DAX.
Any thoughts?
@Andrewutter12 , an error arises when more than one result correspond to Debtors[Tranns ID New]. You may try this formula in a calculated column,
Column =
MAXX (
FILTER (
z_ISG_InFocusProjectQuery,
z_ISG_InFocusProjectQuery[Trans ID New] = Debtors[Tranns ID New]
),
z_ISG_InFocusProjectQuery[ProjectNumber]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
This seemed to do the trick. Thank you so much for your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
25 | |
20 | |
14 | |
8 |
User | Count |
---|---|
71 | |
48 | |
46 | |
20 | |
16 |