Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |