Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gritman
Frequent Visitor

DAX - Joining Tables and Measures

Hi Everyone, 

 

Earlier today I posted a query DAX - How to use a Column Value to use in a Measure 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.DateTable.SalespersonTable.$ AmountMeasure.TotalMeasure.Day %
1st FebBob200100020%
1st FebJessica200120017%
2nd FebBob300100030%
2nd FebJessica400120033%
3rd FebBob500100050%
3rd FebJessica600120050%

 

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.DateTable.SalespersonTable.CostsMeasure.TotalCosts
1st FebBob2060
1st FebJessica3090
2nd FebBob2060
2nd FebJessica3090
3rd FebBob2060
3rd FebJessica3090

 

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.DateTable.SalespersonTable.$ AmountMeasure.TotalMeasure.Day %Table2.TotalCostsDay% x Total Costs (Help)
1st FebBob200100020%6012
1st FebJessica200120017%9015
2nd FebBob300100030%6018
2nd FebJessica400120033%9030
3rd FebBob500100050%6030
3rd FebJessica600120050%9045

 

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.DateTable.SalespersonTable.$ AmountMeasure.TotalMeasure.Day %Table2.TotalCostsDay% x Total Costs (Help)
1st FebBob200100020%15030
1st FebJessica200120017%15025
2nd FebBob300100030%15045
2nd FebJessica400120033%15050
3rd FebBob500100050%15075
3rd FebJessica600120050%15075

 

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

1 ACCEPTED SOLUTION
gritman
Frequent Visitor

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

View solution in original post

1 REPLY 1
gritman
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors