Forum Discussion
Many to Many Measure
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.
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!
4 Replies
Hey,
can you please provide a sample pbix file and share the link to the file on onedrive or dropbox.
Regards
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!
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
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 :)
