We are doing a page to show equivalencies of natural gas saved by implementing energy efficiency measures. As given in the graphic below, how can we insert 6k within the text “Reducing carbon dioxide emissions by 6k metric tons”. ‘6k’ is currently coming from one of the table fields associated to a filter. It can potentially be a dax measure.
Here’s one way to do this using nested CONCATENATE functions (since you can only concatenate two text strings at a time):
Example Label =
VAR FirstString =
CONCATENATE(
"This measure is the concatenation of text plus the value of another measure, ",
[Count Closed Claims]
)
RETURN
CONCATENATE(
FirstString, ", with more text."
)
Interestingly, when I put this example together I found there was more formatting flexibility in using a table visual that card visual in which to drop the measure for display.
I hope this is helpful. Full solution file attached.
- Brian
eDNA Forum - Concatenated Card Solution.pbix (427.9 KB)
1 Like
Thanks Brian. I followed your suggestion and found that the concatenated DAX measure did not format the DAX measures within the concatenation. Any idea how to fix it?
I will try to convince the team including marketing folks to think about the card visual which does not spelt the text out grammatically.
No problem – just add one more concatenate statement using the ROUND function, and concatenate that result with a “K” (see FormattedMeasure variable below).
With regard to the aesthetic look of your card, I think you’ll have much better luck putting it in a stripped down table visual than in a card visual. The former let you better control things like text justification, as well as many other attributes. Just turn off any parts of the table visual you don’t want and/or set the font sizes small as possible and the font color equal to the background color.
-
Brian
Example Label = VAR FormattedMeasure = CONCATENATE( ROUND( [Count Closed Claims]/1000, 0), "K" ) VAR FirstString = CONCATENATE( "This measure is the concatenation of text plus the value of another measure, ", FormattedMeasure ) RETURN CONCATENATE( FirstString, ", with more text." ) -
Brian
1 Like
You could include everything in a measure and do the concat part with an “&” and wrap the measure returning the 6K in a FORMAT function (https://dax.guide/format/)
Example Label =
"This measure is the concatenation of text plus the VALUE of " & FORMAT ( [YourMeasure], "# , 0.00 ""K""" )
2 Likes
Thanks Cristian. I am going to give that a try.
1 Like
This question is similar to the one asked. What Dax would work for including two dynamic fields within this text? 100 and 200 are dynamic measures.
Since 2011, company has helped customers across these sectors and saved more than 100 million therms and earned more than $200 million in incentives.
The following dax works for me. I created a calculated column versus a calculated measure.
Q1: Are there any best practices when to use which for this case?
Q2: How would you create another separate paragraph below for “The company is expected…”
Text =
var roundgross = round([SumGrossTherms]/1000000,0)
var firststring =
“Since 2011, company has helped customers across these sectors save more than “& roundgross&” million therms”
var roundIncentives = round([SumIncentives]/1000000,0)
var secondstring =
“and earn more than “&”$”& roundIncentives&" millions"
return
firststring&" “&secondstring&” "&“The company is expected to result in a cumulative $1 billion in new economic impact, and is forecasted to support 10,000 jobs through 2021.”
Your approach using two variables to capture your calculations to be inserted in the concatenation is spot on. However, I would definitely recommend doing this as a measure rather than via calculated columns. Here’s a sample measure below. Obviously, my two calculations below are just nonsense calculations for illustration - you would sub in the proper calculations/measures in those variables.
Example Label =
VAR Calc1 =
YEAR( Today() ) - 1920
VAR Calc2 =
Calc1 * 2
RETURN
"Since 2011, company has helped customers across these sectors and saved more than " & Calc1 & " therms and earned more than $" & Calc2 & " million in incentives."
I hope this is helpful.
- Brian
1 Like
Thanks BrianJ, can you assist with Q2 on how to establish a separate text using a different paragraph using dax?
Sure, this requires a somewhat obscure trick - the use of UNICHAR(10), which is the UNICHAR code for a hard return. Here’s a sample measure using this trick to return two separate paragraphs in the concatenation:
Example Label =
VAR Calc1 =
YEAR( Today() ) - 1920
VAR Calc2 =
Calc1 * 2
VAR Text1 =
"Since 2011, company has helped customers across these sectors and saved more than " & Calc1 & " therms and earned more than $" & Calc2 & " million in incentives."
VAR Text2 =
"The company is expected to result in a cumulative $1 billion in new economic impact, and is forecasted to support 10,000 jobs through 2021."
RETURN
Text1 & UNICHAR(10) & UNICHAR(10) & Text2
1 Like
Can you please break this thread into two separate ones, with the new thread starting with post #8, since these are distinct questions with different solutions?
Thanks.
- Brian
The paragraphs are still very close and I encountered an error message when I changed 10 to 20.
Text2 =
var roundgross = round([SumGrossTherms]/1000000,0)
var firststring =
“Since 2011, the company has helped customers across these 3 sector and save more than “& roundgross&” million therms”
var roundIncentives = round([SumIncentives]/1000000,0)
var secondstring =
“and earn more than “&”$”& roundIncentives&" millions in incentives."
return
firststring&" "&secondstring& UNICHAR(10) & UNICHAR(10) & “The company is expected to result in a cumulative $1.2 billion in new economic impact and is forecasted to support 8,700 jobs through 2021.”
Yes, if you change the 10 value in UNICHAR(10), it will no longer be read by Power BI as a hard return, and will throw an error message. To increase the spacing between paragraphs, just add more UNICHAR(10) statements where the red arrow points.
Here’s a comparison of the spacing between two UNICHAR(10) statements and four:
Full solution file attached. If you still have difficulties getting the results you want, please post a PBIX file so that I can take a look at exactly what’s needed to get your desired result.
- Brian
eDNA Forum - Concatenated Card Solution2.pbix (429.2 KB)
1 Like
Hi BrianJ, I discovered you used TABLE visual vs the CARD visual. To get the desired result you formatted white for both data label and outline. In conclusion, it worked!
I will try to split the post into two different threads.
Great – glad that worked for you.
Yes, I’ve found that for more complex text formatting the table visual makes a better “card” than the actual card visual.
Don’t worry about splitting the thread - that’s something that only the @EnterpriseDNA team can do.
- Brian
How do I break this thread from here with all the responses?