Forum Discussion
Adding and Linking a column
1. I would like to add an employee column to the Revenue table like in the Salaries table, please assist?
2. Is the calendar table neccesary, if the Revenue table has got a created date?
see pbix: https://drive.google.com/file/d/1KN2fmGx9hsuJaaX0lN5p0NZl_wkhliwE/view?usp=sharing
Hi Yrstruly2021
I have saw your sample, and I will give you some advice for your two questions.
1. You want to add an employee column to Revenue table.
I have two way to build a calculated column to achieve your goal.
Firstly one is to get Employee from other table like Salaries table. But by this way, you need at least a key column in both table to build this calculated column.
Code is similar as below.
Employee = CALCULATE ( MAX ( Salaries[Employee Name] ), FILTER ( Salaries, Salaries[Key Column] = EARLIER ( Revenue[Key Column] ) ) )Or you can try Merge function in Power Query, if you have multipe Employee for same Key Column.
For reference: Combine queries
The second one is to build a calculated column by If function for Revenue table, you need a Key column in Revenue table as well.
Ex:
Employee = SWITCH(Revenue[Key Column],"Value1","Employee1","Value2","Employee2"...)2. If you want to use time intelligence function in dax, or build some visuals with continuous date, you need to build a calendar table with continuous date. So I think a calendar table is necessary for you to build a report.
If this reply still couldn't help you solve your problem, please show me more details about your logic and the result you want.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
not clear about your request. Usually we should have a employee code colun in Revenue or something like that column and you need to have an employee dim table. Then you can connect two tables.
Could you pls elaborate how to get the employee info?
Hi Yrstruly2021
I have saw your sample, and I will give you some advice for your two questions.
1. You want to add an employee column to Revenue table.
I have two way to build a calculated column to achieve your goal.
Firstly one is to get Employee from other table like Salaries table. But by this way, you need at least a key column in both table to build this calculated column.
Code is similar as below.
Employee = CALCULATE ( MAX ( Salaries[Employee Name] ), FILTER ( Salaries, Salaries[Key Column] = EARLIER ( Revenue[Key Column] ) ) )Or you can try Merge function in Power Query, if you have multipe Employee for same Key Column.
For reference: Combine queries
The second one is to build a calculated column by If function for Revenue table, you need a Key column in Revenue table as well.
Ex:
Employee = SWITCH(Revenue[Key Column],"Value1","Employee1","Value2","Employee2"...)2. If you want to use time intelligence function in dax, or build some visuals with continuous date, you need to build a calendar table with continuous date. So I think a calendar table is necessary for you to build a report.
If this reply still couldn't help you solve your problem, please show me more details about your logic and the result you want.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
