cancel
Showing results for
Did you mean:
Regular Visitor

## Finding "pseudo-row" context

I'm trying to create a list for players (called "Members"), where it shows the injuries they have suffered, and within brackets how many times they have suffered a particular type of injury.

```Member_Injuries =
CONCATENATEX(
SUMMARIZE(
NATURALINNERJOIN(
'Members';
'Injuries'
);
Injuries[Injury Type]
);
'Injuries'[Injury Type] &
" (" &
CALCULATE(
COUNTROWS(
NATURALINNERJOIN(
'Members';
ALLEXCEPT(
Injuries;
Injuries[Injury Type]
)
)
)
) &
")";
", "
)```

So, the result I want is the following:

```Member	Injuries
Abc	Injury X (2)
Def	Injury Y (4)
Ghi	Injury X (3), Injury Z (2)```

What I get, however, is the following here:

```Member	Injuries
Abc	Injury X (2)
Def	Injury Y (4)
Ghi	Injury X (5), Injury Z (5)```

I can't figure out a way to find the "pseudo-context" of the injury within the field. Since it's not a row on it's own, I need to create it, I guess, but I don't know how to do that.

Members is just a table with Member ID (as well as some other columns not relevant here), and Injuries is a table with Member ID (with a relationship to the Members table), the Injury Type (and some non-relevant information).

Any suggestions for how I can accomplish this, or where to learn how?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Finding "pseudo-row" context

the row context already exists when you first summarize, so I think it makes sense to calculate the number of rows at that time

`Member_Injuries :=CONCATENATEX ( ADDCOLUMNS ( SUMMARIZE ( NATURALINNERJOIN ( 'Members'; 'Injuries' ); Injuries[Injury Type] ); "NrOfInjuries"; CALCULATE ( COUNTROWS ( 'Injuries' ) ) ); 'Injuries'[Injury Type] & " (" & [NrOfInjuries] & ")"; ", ")`
2 REPLIES 2
Super User

## Re: Finding "pseudo-row" context

the row context already exists when you first summarize, so I think it makes sense to calculate the number of rows at that time

`Member_Injuries :=CONCATENATEX ( ADDCOLUMNS ( SUMMARIZE ( NATURALINNERJOIN ( 'Members'; 'Injuries' ); Injuries[Injury Type] ); "NrOfInjuries"; CALCULATE ( COUNTROWS ( 'Injuries' ) ) ); 'Injuries'[Injury Type] & " (" & [NrOfInjuries] & ")"; ", ")`
Highlighted
Regular Visitor

## Re: Finding "pseudo-row" context

Yes, thank you, works like a charm!