cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Feilin Regular Visitor
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
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
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
Feilin Regular Visitor
Regular Visitor

Re: Finding "pseudo-row" context

Yes, thank you, works like a charm!