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.
Hi All
Please see attached an excel report showing the premier league football players . I want to create a dynamic text narrative that tells me the player who has the most assist . e.g Harry Kane has the most assists of 11
The dynamic texts are underlined and in bold from example above (details below)
How do I go about creating a dynamic text
Many thanks
Solved! Go to Solution.
Hi @atin
Download this PBIX with sample data and working code/visual
Try this Measure instead
Player With Most Assists =
CALCULATE( MAX('Table'[Player Name]), TOPN(1, ALL('Table'))) & " has the most assists of " & CALCULATE( MAX('Table'[Most Assists]), TOPN(1, ALL('Table')))
Regards
Phil
Proud to be a Super User!
Many thanks for the solution. I have one further question relating to this example
If I want to include the team Harry Kane plays for, how do I do this. One of the columns in the data set has the TEAMS included.
I want my narrative to say Harry Kane plays for Tottenham and has the most assists of 11
Many thanks
Hi @atin
Download this sample PBIX file
If your table looks like this
then use this measure
Player With Most Assists =
CALCULATE( MAX('Table'[Player Name]), TOPN(1, ALL('Table'))) & " plays for " & CALCULATE( MAX('Table'[Teams]), TOPN(1, ALL('Table'))) & " and has the most assists of " & CALCULATE( MAX('Table'[Most Assists]), TOPN(1, ALL('Table')))
to give this result
Regards
Phil
Proud to be a Super User!
Hi @atin
Download this PBIX with sample data and working code/visual
Try this Measure instead
Player With Most Assists =
CALCULATE( MAX('Table'[Player Name]), TOPN(1, ALL('Table'))) & " has the most assists of " & CALCULATE( MAX('Table'[Most Assists]), TOPN(1, ALL('Table')))
Regards
Phil
Proud to be a Super User!
Many thanks for the solution. I have one further question relating to this example
If I want to include the team Harry Kane plays for, how do I do this. One of the columns in the data set has the TEAMS included.
I want my narrative to say Harry Kane plays for Tottenham and has the most assists of 11
Many thanks
Hi @atin
Download this sample PBIX file
If your table looks like this
then use this measure
Player With Most Assists =
CALCULATE( MAX('Table'[Player Name]), TOPN(1, ALL('Table'))) & " plays for " & CALCULATE( MAX('Table'[Teams]), TOPN(1, ALL('Table'))) & " and has the most assists of " & CALCULATE( MAX('Table'[Most Assists]), TOPN(1, ALL('Table')))
to give this result
Regards
Phil
Proud to be a Super User!
Hi @PhilipTreacy - Many thanks for the solution provided above.
I have another question relating to the solution and example used in the sample pbix file above Where 2 players have the same number of most assists eg Kevin De Bruyne (Man City) & Harry Kane(Tottenham) both have 11.
How do I create a narrative which says
Kevin De Bruyne, Man City player and Harry Kane, Tottenham player have the most assists of 11
Many thanks
Hi @amitchandak Many thanks for responding back. There appears to be an error when creating the measure. The error says:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
See measure below:
Player with Most Assists =
VALUES('Table A'[Player Name]) ))
@atin , a new measure like
meausre =
CALCULATE(Max(Table[player name]),TOPN(1,all(Table[Player Name]),[all Assists],DESC),VALUES(Table[Player Name]))
& " Most assists "
&
CALCULATE([all Assists],TOPN(1,all(Table[Player Name]),[all Assists],DESC),VALUES(Table[Player Name]))
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 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |