Using Dynamic text Narratives

[atin]

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 

[PhilipTreacy · Accepted 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

[atin · Accepted solution]

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 

[PhilipTreacy · Accepted 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

[atin · Accepted solution]

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 

[PhilipTreacy · Accepted solution]

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]

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 

[atin]

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]) ))

[amitchandak]

@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]))