Dynamic texts in Power BI

I continue to play around with Power BI alongside with generative AI and am looking some very fascinating things from AI (CoPilot in this instance). Today I want to write about dynamic texts where the state name and the URL changes in the citations to the FRED site.

There is no denying that the AI is really helping me to cut the time down shorter.

Layout of design

Let’s first show the image of the current page in the Power BI that I’m working on.

Image 1 is very similar to the image in the post before the last one:

There is an addition of a text box, sitting under the state filter.

Let’s do a close up of that text box seen in Image 1:

This is the citation to the FRED site. Every time someone changes the state, the citation needs to dynamically update to the requested state. Previously, I’ve been hard coding the text but I really need it to dynamically change.

First step to dynamic texts: create StateLookup table

There may be alternative routes to solving this problem, but this is what CoPilot suggested to me: first step is to create a table containing series ID (FRED’s own ID for each dataset) (such as OHUR), 2 alpha state code (OH), and long form state name (Ohio).

This is the part that is new to me: you create it by inserting a new table through the Modeling section of the menu.

Here’s the code to use to create that table:

StateLookup = 
DATATABLE(
    "StateName", STRING,
    "StateCode", STRING,
    "SeriesID", STRING,
    {
        {"Alabama","AL","ALUR"},
        {"Alaska","AK","AKUR"},
        {"Arizona","AZ","AZUR"},
        {"Arkansas","AR","ARUR"},
        {"California","CA","CAUR"},
        {"Colorado","CO","COUR"},
        {"Connecticut","CT","CTUR"},
        {"Delaware","DE","DEUR"},
        {"Florida","FL","FLUR"},
        {"Georgia","GA","GAUR"},
        {"Hawaii","HI","HIUR"},
        {"Idaho","ID","IDUR"},
        {"Illinois","IL","ILUR"},
        {"Indiana","IN","INUR"},
        {"Iowa","IA","IAUR"},
        {"Kansas","KS","KSUR"},
        {"Kentucky","KY","KYUR"},
        {"Louisiana","LA","LAUR"},
        {"Maine","ME","MEUR"},
        {"Maryland","MD","MDUR"},
        {"Massachusetts","MA","MAUR"},
        {"Michigan","MI","MIUR"},
        {"Minnesota","MN","MNUR"},
        {"Mississippi","MS","MSUR"},
        {"Missouri","MO","MOUR"},
        {"Montana","MT","MTUR"},
        {"Nebraska","NE","NEUR"},
        {"Nevada","NV","NVUR"},
        {"New Hampshire","NH","NHUR"},
        {"New Jersey","NJ","NJUR"},
        {"New Mexico","NM","NMUR"},
        {"New York","NY","NYUR"},
        {"North Carolina","NC","NCUR"},
        {"North Dakota","ND","NDUR"},
        {"Ohio","OH","OHUR"},
        {"Oklahoma","OK","OKUR"},
        {"Oregon","OR","ORUR"},
        {"Pennsylvania","PA","PAUR"},
        {"Rhode Island","RI","RIUR"},
        {"South Carolina","SC","SCUR"},
        {"South Dakota","SD","SDUR"},
        {"Tennessee","TN","TNUR"},
        {"Texas","TX","TXUR"},
        {"Utah","UT","UTUR"},
        {"Vermont","VT","VTUR"},
        {"Virginia","VA","VAUR"},
        {"Washington","WA","WAUR"},
        {"West Virginia","WV","WVUR"},
        {"Wisconsin","WI","WIUR"},
        {"Wyoming","WY","WYUR"}
    }
)

The wonderful thing about the CoPilot was that I didn’t have to waste time looking up all 50 states and their 2 alpha codes. CoPilot did that for me. CoPilot gave me that code; all I had to do was copy and paste.

Second step: create the Citation measure

CoPilot gave me the following DAX code to create the CItation measure:

Citation = 
VAR SelectedState =
    SELECTEDVALUE(StateLookup[StateName])

VAR SeriesID =
    SELECTEDVALUE(StateLookup[SeriesID])

VAR _URL =
    "https://fred.stlouisfed.org/series/" & SeriesID

VAR RetrievedDate =
    FORMAT(TODAY(), "MMMM D, YYYY")

RETURN
    "U.S. Bureau of Labor Statistics, Unemployment Rate in "
    & SelectedState & " [" & SeriesID & "], retrieved from FRED, Federal Reserve Bank of St. Louis; "
    & _URL & ", " & RetrievedDate & "."

I did have a little bit of an issue with the URL when I wrote the URL variable as URL rather than _URL. It turns out URL is already reserved in Power BI and thus cannot be used. By a fluke, I eventually landed on _URL as the variable and CoPilot finally said, “yep, that’s the way to go”.

Generative AI frequently gets you up to 90% of the way and you have to figure out the last mile to your destination.

Third step: embedding the Citation measure into text

Here is where I had a LOT of trouble figuring out how to do this, despite CoPilot efforts to help me.

It seems to me that there are different version so Power BI and I don’t know which one I have. CoPilot talked a lot about “rich text” which I could not find in my version of Power BI.

Or the issue could have been that CoPilot left off one key aspect that I need to look for which was “title”. In other words, I had to insert Citation measure into the title of the text box.

To explain this better, I will add images to showcase the route I had to go through.

And that’s it. You now have a text box that updates the citations based upon what has been selected in the state filter.

Change the state

Here’s how the page in Power BI looks when I select a different state. I’m enlarging the image so you can see the text box better. Select a new state and immediately, the text box updates.

I’m really enjoying using the AI to help me navigate around Power BI and solve some of the more stickier problems.