Forum Discussion

TAP936's avatar
TAP936
Icon for Helper I rankHelper I
5 years ago

Multiplying one table by another which is not unique

Hi, I could really do with some help please;

 

I have two tables

Table 1 contains Home Venue, Away Venue and Ticket Qty

Table 2 contains Home Venue, Away Venue and Mileage ( between home and away)

 

I have a many to many relationship between table 1 & 2 using home venue

 

I have created a calculated column in table 1 to show quantity where away venues are the same

=SUMX(FILTER('table 2',[Away venue]=Table1[Away venue]),'Table 1'[Quantity]) this has worked fine

 

I now need to multiply the quantity in Table 1 by the mileage in Table 2 (not unique) 

Table 1 Table 2 
Home VenueAHome VenueA
Away VenueBAway VenueB
Quantity10Mileage100

so in the example above - 10 * 100 = 1000

I'm stuck on how to do this ( it seems like it should be simple), can anyone point me in the right direction?

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    TAP936 

     

    Many-To-Many relationships in PBI are designed to solve granularity issues and ONLY THAT. If you use them for anything else... you are not doing it right. Please read THIS to know how to create good, understandable and predictable models.