Hi............I'm working with our GL history table and trying to match it up with the GP table that has account name & #. I built the relationship between the ACTINDX field. I used merge tables and it did not return the result I expected. I want to bring back the ACTNUMST field into the other table so I can categorize by sales, COGS, etc. What am I missing? FYI - new to PowerBI.
Solved! Go to Solution.
ok, so if I understand properly, you are keen to have a column/field in your [GL History] table use a value from the [GL00105] table and you have a relationship.
Try this and let me know how you get on.
Add a calculated column to your [GL History] table, something like this
Test Column = RELATED(GL00105[ACTNUMST])
If that works the way you expect you can incorporate that logic in formulas that include other columns in your [GL History] table.
Hi @gskoglund
You can possibly use VLOOKUP but it might not be the most efficient approach. When you say you created a relationship, do you mean in DAX?
Thanks for responding Phil. No through the relationship builder. From my understanding and again newbie here, if you created a relationship between the 2 tables with the same field that it would merge. I would like the most efficient approach. 😉 I've gone down a few different avenues and I guess just a little confused.
They won't merge the tables as such, but you can write formulas using columns from both tables.
Are you trying to create a calcuated column? If so, in which table?
Maybe I'm misunderstanding what a calculated column is. In my GL history table I have the ACTINDX field which represents an account #. I have the account # in the GL00105 table, which also references the ACTINDX field. So I would like to get the ACTNUMST fromt he GL00105 table over to the other table so I can see everything by acct # vs. an index #. Normally I would just do this through a vlookup in Excel.
ok, so if I understand properly, you are keen to have a column/field in your [GL History] table use a value from the [GL00105] table and you have a relationship.
Try this and let me know how you get on.
Add a calculated column to your [GL History] table, something like this
Test Column = RELATED(GL00105[ACTNUMST])
If that works the way you expect you can incorporate that logic in formulas that include other columns in your [GL History] table.
Thanks Phil! That is what I was looking for. I saw that Related before but there was a disconnect for some reason. Thanks for your help!
User | Count |
---|---|
143 | |
84 | |
64 | |
61 | |
57 |
User | Count |
---|---|
211 | |
109 | |
89 | |
76 | |
74 |