Forum Discussion
Create Calculated Column from Related Table
2 tables related via the Account ID column.
Table 1 is a dimension table (GL Accounts) and has over 100 rows.
Account ID | Account Name |
... |
|
5-1000 | Wages |
5-1200 | Contract Labour |
5-1300 | Superannuation |
5-1350 | Workcover |
... |
|
Table 2 is a Fact table (Remuneration) and uses less than 10 of the rows in the GL Accounts table
Account ID | Date | Amount | Calculated Column Account Name |
5-1000 | 02/01/2018 | 10065.33 | Wages |
5-1200 | 02/01/2018 | 4344.31 | Contract Labour |
5-1300 | 02/01/2018 | 1198.12 | Superannuation |
5-1350 | 02/01/2018 | 129.82 | Workcover |
5-1000 | 09/01/2018 | 9892.23 | Wages |
5-1200 | 09/01/2018 | 3998.76 | Contract Labour |
5-1300 | 09/01/2018 | 976.45 | Superannuation |
5-1350 | 09/01/2018 | 102.34 | Workcover |
5-1000 | 16/01/2018 | 11234.55 | Wages |
5-1200 | 16/01/2018 | 4844.67 | Contract Labour |
5-1300 | 16/01/2018 | 1324.98 | Superannuation |
5-1350 | 16/01/2018 | 145.32 | Workcover |
I want to use Account Name in a Slicer but the list is too long if I use the GL Accounts table.
I'm figuring if I create a Calculated Account Name column in the Remuneration table (as above) then I can have a Slicer refer to that column then it will display the options I want.
I have not been able to find a solution to this problem on the Forum.
Can anyone help me with a formula to do this?
grawri,
Please use DAX below.Column = LOOKUPVALUE('GL Accounts'[Account Name],'GL Accounts'[Account ID],Remuneration[Account ID])
Regards,
Lydia
