topic Re: Add values into a different table in DAX Commands and Tips

Add values into a different table

Dimitris_Kats — Mon, 07 Feb 2022 14:56:13 GMT

Dear members hello,

I have a really difficult problem that I have not yet been able to solve.

I have 2 unrelated fact tables. The one table has salary data and the second table expenses data.
Salary Table:
Employee ID Salary Period ID Country ID
1000 1000 20210101 1
1001 900 20210101 2
1002 800 20210101 3
1003 1100 20210101 4
1004 1200 20210101 5
1000 1000 20210201 1
1001 900 20210201 2
1002 800 20210201 3
1003 1100 20210201 4
1004 1200 20210201 5

Expenses Table:
Employee ID Expenses Category Period ID Country ID
1000 100 Tickets 20210101 1
1001 200 Food 20210101 2
1002 100 Taxi 20210101 3
1003 200 Car 20210101 4
1004 50 Tickets 20210101 5
1000 20 Food 20210201 1
1001 35 Taxi 20210201 2
1002 60 Car 20210201 3
1003 80 Food 20210201 4
1004 100 Taxi 20210201 5

I would like to find a way to add the salary data of employee with id: 1001 to the expenses table as expenses to the employee with id 1002 at the same period ID and Country ID in the category "Car" if it exist otherwise to be filled in as a car category.

Is that possible?? I can't find a way to achive that 😞

Re: Add values into a different table

amitchandak — Mon, 07 Feb 2022 16:04:20 GMT

Dimitris_Kats , can explain the car part. May be expected output can help

You can create a new column like

sumx(filter(salary, salary[Employee ID] = expense[Employee ID] && salary[Period ID] = expense[Period ID]

&& salary[Country ID] = expense[Country ID]),Salary [Salary])

sumx(filter(salary, salary[Employee ID] = expense[Employee ID] && salary[Period ID] = expense[Period ID]

&& salary[Country ID] = expense[Country ID] && expense[Category] = "Car"),Salary [Salary])

Re: Add values into a different table

v-jingzhang — Thu, 10 Feb 2022 09:43:06 GMT

Hi Dimitris_Kats

You can use Power Query to do this as you need to add new rows when employee 1002 doesn't have car category with some period and country IDs. Click "Transform data" to open Power Query Editor to transform tables.

Here are my steps to transform both tables:

Salary Table:

1. Filter "Employee ID" column to only have rows that Employee ID = 1001;

2. Replace "1001" with "1002" in "Employee ID" column;

3. Add a custom column whose value is "Car", column name is "Category";

4. Rename "Salary" column to "Expenses".

Now you will have below table, which has five columns whose names are totally consistent with those in Expenses Table. This is Employee 1002's Car expenses in every period and country.

Expenses Table:

5. Append above transformed Salary Table to Expenses Table;

6. Group rows by four columns and perform Sum on "Expenses" column;

7. Sort Rows.

Then you will have the following result. ( * In your sample data, Employee 1001 has country ID 2 and 1002 has country ID 3, which cannot be matched. So I changed both to country ID 2 to test it. )

In the following image, I have highlighted most features used to transform tables in above steps. Sample pbix is at bottom.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Re: Add values into a different table

Dimitris_Kats — Mon, 21 Mar 2022 08:38:43 GMT

Thank you very very much.

This is really complex. I need to test it and come back to you.
So sorry for the late reply. I didn't get a notice for your post.

Thank you very much again!