add column from not direct linked table

[antonio_wurth]

hello 

help please

i have 2 tables

there is no relation between them

but i need to add column based on value from table

in file example with more detailed requirements explanation

https://1drv.ms/u/s!Asg9-7-728LO1yM5-tw6ByxQkGbM?e=fOUY9A

thank you in advance

[antonio_wurth]

i added formula 

work_hours =

var p_employee_id = MAX(dim_clients[main_sales_manger_id])

RETURN

sumx(

    FILTER(fact_employees_days_off,

    fact_employees_days_off[employee_id] = p_employee_id), fact_employees_days_off[day_off])

   

but not everywhere getting expected result
here an empty value...

[antonio_wurth · Accepted solution]

looks i received expected result

work_hours =

var p_employee_id = VALUES(dim_clients[main_sales_manger_id])

return

sumx(

    FILTER(fact_employees_days_off,

    fact_employees_days_off[employee_id] IN p_employee_id), fact_employees_days_off[day_off])

[Anonymous]

Hi @antonio_wurth ,

I am sorry that I can not open the link but I can give you some helps about add column from not direct linked table.

I create two tables in the example.

They have no relationship.

There are two methods. The first one is create index column in Power Query. By the index column, we can connect the two tables.

This way we can use Merge Queries.

Another method is using DAX. We can use UNION fuction.

Table 2 =
UNION (
    SELECTCOLUMNS ( 'Table', "1", 'Table'[Name], "2", 'Table'[Price] ),
    SELECTCOLUMNS ( 'Table (2)', "4", 'Table (2)'[Name], "3", 'Table (2)'[Price] )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[antonio_wurth]

@Anonymous 

thank you

yes i'm also thinking about join them but on sql(dwh) level

but whant to know as well if its possible to achieve that result without any join

[Anonymous]

Hi @antonio_wurth ,

It seems that we can not achieve that result without any join, because we need to use index to build relationships between the two tables. Otherwise we should build relationships between two tables.

I think you can also use UNION function.

Table 2 =
UNION (
    SELECTCOLUMNS ( 'Table', "1", 'Table'[Name], "2", 'Table'[Price] ),
    SELECTCOLUMNS ( 'Table (2)', "4", 'Table (2)'[Name], "3", 'Table (2)'[Price] )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[antonio_wurth]

i'm trying to achive a bit different 

i have two tables

first table with columns:

client, employee, managers, sales_amount

second with

employee, manager, work_hours

i need to buid table based on first table with 

client, employee, managers, sales_amount

and lookup value of work_hours from second table by employee key

so visual shoul be as:

client, employee, managers, sales_amount, work_hours

like a left join on SQL or vlookup(or even sumif) in excel