Forum Discussion
Using Dynamic text Narratives
Player with the most assists
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)
- Harry Kane
- Most Assists
- 11
How do I go about creating a dynamic text
Many thanks
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
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
6 Replies
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
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
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]))
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 =
CALCULATE(MAX('Table A'[Player Name]), TOPN(1, ALL('Table A'[Player Name] ) , ALL('Table A'), DESC) ,VALUES('Table A'[Player Name] ) )& "Most assists" &CALCULATE(ALL('Table A'[Assists]), TOPN(1, ALL('Table A'[Player Name] ), ALL('Table A'), DESC,VALUES('Table A'[Player Name]) ))
