Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
User | Count |
---|---|
17 | |
16 | |
14 | |
13 | |
12 |
User | Count |
---|---|
17 | |
14 | |
12 | |
10 | |
9 |