cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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]
& ")";
", "
)
4 REPLIES 4
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]
& ")";
", "
)
Feilin Regular Visitor
Regular Visitor

Re: Finding "pseudo-row" context

Yes, thank you, works like a charm!

PSRai Frequent Visitor
Frequent Visitor

Re: Finding "pseudo-row" context

Hi 

 

I have a pseudo code which I need to put into Power bi. As Im new to both I need some help with putting the code in Pbi

 

The energy reading im pulling out of the database vary from 2-6 seconds per minute. I need to fill in th egaps so that their are readings every minute to work out the KWH (kilo watts/hour)

 

Array  Readings[Num_Energy_Readings]

Array CorrectedReadings[Num_Energy_Readings]

Count =0

EnergyAdjust = 0

TimeOffset=Readings[0]              ‘ Set energy to 0 at the first time required to clear previous energy

CorrectedReadings[0]=Readings[0]-TimeOffset    ‘ Load first readings

For count=1 to count = Num_Energy_Readings

{

                If Readings[count] <  Readings[count-1]

                {

                                EnergyAdjust=EnergyAdjust + Readings[count-1]

                }

                CorrectedReadings[count]=Readings[count]+EnergyAdjust[]-TimeOffset

}

Super User
Super User

Re: Finding "pseudo-row" context

@PSRai 

I'd suggest creating a new subject post with some anonymised data samples, as described here:

How to Get Your Question Answered Quickly