Summarize with M2M relationships

[AlejandroPCar]

Hi,

How can I create a SUMMARIZE between two tables whose have a Many to Many relationship? Right-hand activities table vs left-hand people table. The idea is to know how many activities can a person achieve. The common column is the groupID, so a person can be in only 1 group, but a group is composed of many activities. 

Thanks for your help.

[LivioLanzo]

Hello @AlejandroPCar,

could you post a sample of your data?

thanks

[AlejandroPCar]

Hi @LivioLanzo

Here is a test of my data: https://1drv.ms/u/s!AtTnrgPUQzQCgrNz52irmAcjEaHXqw

[LivioLanzo · Accepted solution]

Hello @AlejandroPCar 

You need to create a Group table with this DAX query:

Groups = 
DISTINCT( UNION( ALLNOBLANKROW( Activities[groupID] ), ALLNOBLANKROW( People[groupID] ) ) )

then create these relationships:

then drop the person ID on the matrix rows and add this measure:

Measure = 
CALCULATE(
    COUNTROWS( Activities ),
    CROSSFILTER( People[groupID], Groups[groupID], Both )
)

[AlejandroPCar]

Hello @LivioLanzo

It works enough well. Thanks a lot.