DAX - Joining Tables and Measures

gritman — Mon, 20 May 2019 06:03:55 GMT

Hi Everyone,

Earlier today I posted a query and received a great solution from parry2k. I would appreciate help with the next step in my PowerBi report.

I now have a table2 with the below.

Table.Date	Table.Salesperson	Table.$ Amount	Measure.Total	Measure.Day %
1st Feb	Bob	200	1000	20%
1st Feb	Jessica	200	1200	17%
2nd Feb	Bob	300	1000	30%
2nd Feb	Jessica	400	1200	33%
3rd Feb	Bob	500	1000	50%
3rd Feb	Jessica	600	1200	50%

And another table with the below (I used a simular formula for the Total as per the above table). I've joined the tables by a 'KeyTable' which has two entries - Bob and Jessica.

Table.Date	Table.Salesperson	Table.Costs	Measure.TotalCosts
1st Feb	Bob	20	60
1st Feb	Jessica	30	90
2nd Feb	Bob	20	60
2nd Feb	Jessica	30	90
3rd Feb	Bob	20	60
3rd Feb	Jessica	30	90

What I would like to do is link the two tables, by Salesperson, and multiply the Measure.Day% from the first table with Measure.Total in the second table, so I get the below.

Table.Date	Table.Salesperson	Table.$ Amount	Measure.Total	Measure.Day %	Table2.TotalCosts	Day% x Total Costs (Help)
1st Feb	Bob	200	1000	20%	60	12
1st Feb	Jessica	200	1200	17%	90	15
2nd Feb	Bob	300	1000	30%	60	18
2nd Feb	Jessica	400	1200	33%	90	30
3rd Feb	Bob	500	1000	50%	60	30
3rd Feb	Jessica	600	1200	50%	90	45

At the moment I have the 'TotalCosts' coming back - but it's a sum of the Costs in Table2, rather then those Bob costs and Jessica costs

Table.Date	Table.Salesperson	Table.$ Amount	Measure.Total	Measure.Day %	Table2.TotalCosts	Day% x Total Costs (Help)
1st Feb	Bob	200	1000	20%	150	30
1st Feb	Jessica	200	1200	17%	150	25
2nd Feb	Bob	300	1000	30%	150	45
2nd Feb	Jessica	400	1200	33%	150	50
3rd Feb	Bob	500	1000	50%	150	75
3rd Feb	Jessica	600	1200	50%	150	75

I've be grateful for advice anyone can offer. Let me know if any more info or the queries I have so far would be useful.

Thanks

Andy

Re: DAX - Joining Tables and Measures

gritman — Mon, 20 May 2019 07:21:16 GMT

I've managed to solve this one myself. I changed the join type to be "Cross Filter Direction" = Both (instead of Single) and that gave me what I was looking for.

Thanks

topic Re: DAX - Joining Tables and Measures in DAX Commands and Tips

DAX - Joining Tables and Measures

Re: DAX - Joining Tables and Measures