(scottish) sql bob blog

rss

Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.


Create dynamic text with M (Power Query Formula Language)
This post is wholly inspired by this post (https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/) by Chris Webb about creating chart titles.  We have a set of reports which are the same for each client, so when we are asked to create set of reports for a new client.  The process is relatively straightforward and simple.  One frustration is that we had to change the client name in a text box at the top of each page.  l am lazy so as programmer l like the computer to do the work for me.

So first l created a variable in the query window see figure 1.


Figure 1 – Select 'Blank Query' from the new source options

When the  blank query window opens, replace the text 'Query' with the name of the variable, then enter the value that will be returned from the variable.    See Figure 2 for details  


Figure 2 – Showing location of the variable name and where value should be placed.

In figure 3 what l have done is to enter the client name that will be used in the example 'Rainbows' and changed the variable name to 'ClientName'.  Once completed those details then click on the 'Close & Apply' button.


Figure 3 – Showing the client name and variable name.

Once returned to the main PowerBI desktop window, find the newly created dataset in this example it is called 'ClientName' with a field called ' ClientName'.  Now that the variable has been surfaced its time to use.  So let create a new measure, expand out the dataset click once with mouse on the ellipsis and select 'New measure' from the list see figure 4.


Figure 4 – Creating a new measure

When the new measure dialog box appears in this example l created a measure called 'TicketsTextBox' as  l want to the measure to appear in a box containing text.  Which is the reason for my naming convention.  The measure l used is as follows

TicketsTextBox = CONCATENATEX(ClientName,[ClientName]) & "- Tickets"  
 
In figure 5 you can see the newly created measure.


Figure 5 – showing the measure 'TicketsTextBox'

The next step is to get this measure on the report canvas, so insert a card visualization on the report canvas.  Then drag the measure to the values section on the visualization configuration section.  Click on the paint brush icon and expand out the 'Data label' section adjust the size of the label to the desired size.  Then turn off the 'Category Label.  These steps are shown in the video below.

Now you have a card with dynamic title, if you want to change the client name all you have to do is change the text in the variable. A sample PowerBI report file can be downloaded here which contains the report shown above.












b i u quote


Save Comment
Showing 0 Comment