Dynamic text?

[DLB13]

Hi all - 

I have a bit of an atypical use for Power BI I'm trying to find a workaround for, even though I know it's not an ideal use. Basically, I need to display several sentences of text depending on what slicers (there are 5 or so) are selected. I also need to have placeholder text that displays when no slicers are selected and, ideally, when whatever slicer combo is selected does not have text that goes with it.

I'm using Text Enhancer by MAQ Software, which is basically just the card visual with additional text editing capabilities. How I've structured it so far is I have a spreadsheet with a project number, the card the text should be displayed on, and the text itself. I also have a separate spreadsheet the project number relates to that has metadata about the project, like year and country--these are some of the slicers. I've copied below a very basic dummy data set of how the data is organized in Excel. When making the visual in Power BI, I put the "text" as the field, and the card location as a filter. I've also put in placeholder text (project 0 in the dataset) that will display when nothing is selected, as it's the first data in the data set.

This somewhat works, but I have encountered a couple of issues. Thus, I'm wondering if anyone has any alternative suggestions on how to display "dynamic" text like this? I imagine this isn't the only possibility. The challenges with my approach are:

• "First" is not always displaying the first value (I opened a separate thread about this). It works most of the time, but for some reason, it's not on a couple of the cards.
• "0" displays if the slicer combination selected does not return an entry (in the example data set, for instance, if USA and 2020 were both selected). While in theory I could duplicate the placeholder text, it's not feasible to create placeholder text in the spreadsheet for all possible slicer combinations (with five slicers including country and year, that list is reallll long).

Looking forward to any thoughts and suggestions! Many thanks in advance!

Text spreadsheet:

Project Number	Card Location	Text
0	1	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.
0	2	Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
0	3	Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
0	4	Amet dictum sit amet justo donec enim diam. Dapibus ultrices in iaculis nunc sed augue lacus viverra vitae.
99	1	Blandit turpis cursus in hac habitasse platea.
99	2	Vitae suscipit tellus mauris a diam maecenas.
99	3	Sit amet purus gravida quis blandit turpis cursus in hac.
99	4	Velit aliquet sagittis id consectetur purus.
101	1	At urna condimentum mattis pellentesque id nibh.
101	2	Dis parturient montes nascetur ridiculus mus mauris. Mattis aliquam faucibus purus in massa tempor nec feugiat nisl.
101	3	Nec nam aliquam sem et tortor consequat id porta nibh.
101	4	Vel pretium lectus quam id leo in.

Metadata:

Project Number	Country	Year
99	USA	2019
101	France	2020

[Queryon]

I built out a little Lorem Ipsum Text Generator if it's helpful.  If you need the PBIX file showing how it works, feel free to ping me.  

Ipsum = 
/* -------------------------------------------------------------------------------------------------------------------------
 Title:   Random Ipsum Lorem Text Generator.  
 Desc:    Useful for creating placeholder data without dependency on other tables or data sets. 
 Author:  Queryon // Orion Matthews. orion.matthews@queryon.com
 License: This code is free to remix/reuse. Licensed under MIT licence. https://opensource.org/licenses/MIT
 
 www.queryon.com                                                                                     Keep Calm and Queryon
 -------------------------------------------------------------------------------------------------------------------------*/

// Set the following to the same to return constant length string
var CHARACTERS_TO_RETURN_MIN = 500
var CHARACTERS_TO_RETURN_MAX = 500
var NO_SPACES = FALSE() // Useful if you are doing a random name

// standard ipsum paragraph in use since 1500s
var ipsum = "lorem ipsum dolor sit amet consectetur adipiscing elit sed do eiusmod tempor incididunt ut labore et dolore magna aliqua ut enim ad minim veniam quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur excepteur sint occaecat cupidatat non proident sunt in culpa qui officia deserunt mollit anim id est laborum"

// Duplicate the string a bit so we can pick a random spot
var ipsum2 =CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(ipsum,   ipsum), ipsum), ipsum), ipsum), ipsum), ipsum), ipsum) // this gives us a length of 3,496 to work with 

// Remove spaces if the NO_SPACES variable is set
var ipsum3 = IF(NO_SPACES=true, SUBSTITUTE(ipsum2, " ", ""), ipsum2)

// Pick out a random chunk and trim it up so their isn't spaces at the start or end. 
return trim(mid(ipsum3, RANDBETWEEN(1,500)+500, RANDBETWEEN(CHARACTERS_TO_RETURN_MIN, CHARACTERS_TO_RETURN_MAX)))

/* Bug and notes: ¯\_(ツ)_/¯
 Trim bug - Due to trimming, if MIN and MAX are set to the same it might return one or two less than exactly when spaces are allowed.
 Upper case letter bug - Can't figure out how to upper case the first letter due to random number not being declarable as a variable.  Write me if you have a solution!
 */

[AlexisOlson · Accepted solution]

The basic approach would be to write a measure like

TextToDisplay = SELECTEDVALUE ( Sheet1[Text], "Placeholder Text" )

This will return the text value if there is only one in the current filter context and the placeholder text otherwise. For your dataset, you'd need filters or slicers set on both Project Number and Card Location, or else the text won't be unique and the measure would return the placeholder text.

[DLB13]

Thank you, this is exactly what I needed!