Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
7 years ago
Solved

Summarize with M2M relationships

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.

Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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 )
)

4 Replies