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 pe...
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 )
)
LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
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 )
)