Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |