Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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]
& ")";
", "
)
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
}
I'd suggest creating a new subject post with some anonymised data samples, as described here:
How to Get Your Question Answered Quickly
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]
& ")";
", "
)
Yes, thank you, works like a charm!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |