Many to Many Measure

[Ruksuro]

Hi Community,

I have created a many-many structure in desktop by creating a bridging table which contains only the distinct values of the joining column.

I would like to create a measure which does the equivalent of SUMPRODUCT, taking each line in Table 1 and mutliplying by the related value in Table 2. Table 2 will be filtered on the dashboard to resolve the many-many issue.

There are over 50 "types" in each table so it must remain a many-many structure (I don't want 50x tables floating about).

Any help appreciated, thanks!

Harry.

[TomMartens]

Hey,

can you please provide a sample pbix file and share the link to the file on onedrive or dropbox.

Regards 

[Ruksuro · Accepted solution]

Hi Tom,

The act of producing a simplified model help the solution come to me! 

I simply needed:

  result measure = SUMX(DISTINCT('Join'[Group]),[Sum Table 1 Pop Measure]*[Sum Probabilities Measure])

I'll PM the dropbox link if you are curious.

Thanks anyway!

[TomMartens]

Perfect, I will have a look tomorrow, because I'm always curious 🙂

Please mark your post with the DAX expression as answer, this may also help others.

Cheers

[Ruksuro]

Hi Tom,

Will do! But I'll have to do it tonight from my home pc for corporate security reasons.

Watch this space!

Thanks again 🙂