Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
atin
Resolver I
Resolver I

Using Dynamic text Narratives

Player with the most assistsPlayer 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 

 

3 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
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')))

most-assists.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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 

View solution in original post

Hi @atin 

 

Download this sample PBIX file

 

If your table looks like this

footy-teams.png

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

footy-meas2.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
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')))

most-assists.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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

footy-teams.png

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

footy-meas2.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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 

 

atin
Resolver I
Resolver I

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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.