Forum Discussion
DLB13
Helper I
Helper IDynamic text?
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 depend...
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.
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. [email protected]
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!
*/