98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Smart narrative
Smart narrative
Resolved · Medium Priority · Version 365
Tue 13 Dec 2022, 15:57
Edited on Tue 13 Dec 2022, 15:59Delegate Matt said...
Matt has attended:
No courses
Smart narrative
Hi
I'm using text boxes with Values functionality to have narrative that updates with my filters. The problem is that I have some values that are plain wrong, despite using the same language as the Smart Narrative feature...Are there tips and tricks for the Value based narrative text boxes? I've made sure there aren't duplicate metrics with the same name in different tables.
Sometimes I get a red error message saying that the narrative "will not return a single value" but it's not clear why.
Thanks
Matt
Tue 13 Dec 2022, 20:25Trainer Jens said...
RE: Smart narrative
Hi Matt,
I do not use the Smart Narrative tool in Power BI.
I make dynamic text boxes controlled by DAX functions. I have never been in a situation, where my DAX text boxes do not return the right text based on filters and DAX measures.
It is difficult for me to help you with this.
The error "will not return a single value" comes when something return more than one value. You will have to use the Hasonevalue function to test if it return more than one value.
Below you can find an example of how my DAX could look like in a Dynamic text box.
Text = "The line chart shows "&if(HASONEVALUE(tblSalesRep[SalesRep]),"the sales rep "&values(tblSalesRep[SalesRep])&"'s","All sales reps'")&" product sales for "&if(HASONEVALUE(Dates[Year])," year "&SELECTEDVALUE(Dates[Year])&"."," all years.")&if(HASONEVALUE(tblSalesRep[SalesRep])," The profit for "&values(tblSalesRep[SalesRep])&"'s","All sales reps'")&" product sales for "&if(HASONEVALUE(Dates[Year])," year "&SELECTEDVALUE(Dates[Year])," all years")& " is " & format([Profit],"currency","en-gb")
If you want to learn how to do this. I am doing it on our one day DAX course.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 14 Dec 2022, 10:39Delegate Matt said...
RE: Smart narrative
Thanks Jens, that's a shame, I'll have to rewrite everything in DAX. I have the example you gave in the training session using a new text metric - can you remind me, does that go into a normal text box or a card, or something else?
Thanks
Matt
Wed 14 Dec 2022, 20:39Trainer Jens said...
RE: Smart narrative
Hi Matt,
The DAX has to be used in text box. Add the Text box and Turn on title under the formatting options in the Format pane to the right. Press Fx next to the text box in the formatting pane and select the DAX.
To get no decimals and thousands separator Format(SUM(XYZ_Workforce[CurrentHeadcount]),"#,##0")
The DAX below will return the name of the best selling product based on a measure called [Sales]. I have the product name in a column called Items[Product Description]. I hope you can translate
the DAX below to your data model.
CONCATENATEX (
TOPN ( 1, VALUES( Items[Product Description] ), [Sales] ),
[Product Description],
";"
)
To make a part of the text bold. Please check the link below
https://community.powerbi.com/t5/Desktop/Part-of-text-to-be-bold-using-DAX/td-p/492204
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 14 Dec 2022, 20:40Trainer Jens said...
RE: Smart narrative
Hi Matt,
The DAX has to be used in text box. Add the Text box and Turn on title under the formatting options in the Format pane to the right. Press Fx next to the text box in the formatting pane and select the DAX.
To get no decimals and thousands separator Format(SUM(XYZ_Workforce[CurrentHeadcount]),"#,##0")
The DAX below will return the name of the best selling product based on a measure called [Sales]. I have the product name in a column called Items[Product Description]. I hope you can translate
the DAX below to your data model.
CONCATENATEX (
TOPN ( 1, VALUES( Items[Product Description] ), [Sales] ),
[Product Description],
";"
)
To make a part of the text bold. Please check the link below
https://community.powerbi.com/t5/Desktop/Part-of-text-to-be-bold-using-DAX/td-p/492204
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 15 Dec 2022, 10:22
Edited on Thu 15 Dec 2022, 14:13Delegate Matt said...
RE: Smart narrative
Couple of quick Qs -
- The format in line 10 (penulitmate) here isn't working - do I need to format the TOPN rather than the CONCATENATEX?
- what is the role of the ";" pls?
TextOverview = "There are "
&Format(SUM(AHR_Workforce[CurrentHeadcount]),"#,##0")
&" people in the AHR health and care workforce, contributing "
&Format(SUM(AHR_Workforce[Current FTEs]),"#,##0")
&" FTEs. The largest organisation type is "
& CONCATENATEX (TOPN ( 1, VALUES( Ref_Organisations[Org Type] ), [FTEs] ), Ref_Organisations[Org Type],";")
&" with x FTEs. The largest staff group is "
& CONCATENATEX (TOPN ( 1, VALUES(AHR_Workforce[Staff Group - Top Level]), [FTEs]), AHR_Workforce[Staff Group - Top Level],";")
&" with "
& FORMAT(CONCATENATEX (TOPN ( 1, VALUES(AHR_Workforce[Staff Group - Top Level]), [FTEs]), [FTEs],";"),"#,##0")
& " FTEs."
Wed 14 Dec 2022, 11:17
Edited on Fri 16 Dec 2022, 14:52Delegate Matt said...
RE: Smart narrative
Hi Jens
I'm slowly getting there :) I need to filter the following so that it only looks at latest quarter - can you help please?
TextVacantGroups = "In the latest quarter, the biggest staff group for vacancies is "
& CONCATENATEX (TOPN ( 1, VALUES( AHR_Workforce[Staff Group - Top Level]), [Current Vacancies] ), AHR_Workforce[Staff Group - Top Level],";")
//I want to filter this to latest quarter only, then add another statement that adds in the value.
Thanks
Matt
Mon 2 Jan 2023, 19:26
Edited on Mon 2 Jan 2023, 19:27Trainer Jens said...
RE: Smart narrative
Hi Matt,
Try
TextVacantGroups = "In the latest quarter, the biggest staff group for vacancies is "
& CONCATENATEX (Calculate(sum([Current Vacancies]),TOPN ( 1, VALUES( AHR_Workforce[Staff Group - Top Level]), [Current Vacancies] ),Previousquarter("Date")), AHR_Workforce[Staff Group - Top Level],";") & " the value is " & Calculate(sum([Current Vacancies]),TOPN ( 1, VALUES( AHR_Workforce[Staff Group - Top Level]), [Current Vacancies] ),Previousquarter("Date"))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 3 Jan 2023, 10:31Delegate Matt said...
RE: Smart narrative
Thanks Jens. I tweaked it to this
TextVacantGroups = "In the latest quarter, the biggest staff group for vacancies is "
& CONCATENATEX (Calculate(sum(BHR_Workforce[CurrentVacancy FTEs]),TOPN ( 1, VALUES( BHR_Workforce[Staff Group - Top Level]), [Current Vacancies] ),MAX(BHR_Workforce[Reporting Quarter]), BHR_Workforce[Staff Group - Top Level],";"))
& " the value is "
& Calculate(sum(BHR_Workforce[CurrentVacancy FTEs]),TOPN ( 1, VALUES( BHR_Workforce[Staff Group - Top Level]), [Current Vacancies] ),MAX(BHR_Workforce[Reporting Quarter]))
and it says that there are too few arguments passed to CONCATENATEX...
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Implement Row-Level Security (RLS)If your reports contain sensitive information, implement Row-Level Security to control access to data at the row level. RLS allows you to define rules that restrict data access based on user roles, ensuring that each user sees only the relevant data according to their permissions. |
