Microsoft – Page 13 – STL

If you want to add dynamic text to give your audience more detailed Power BI reports, you can follow STL’s LinkedIn page. This is first part of the series, but more will follow.

Dynamic

In another series of blog posts from STL about AI visuals, you could see how the smart narrative visual could analyse another visual and generate a text box with useful text (see link below).

https://www.stl-training.co.uk/b/power-bi-ai-visuals-part-4-smart-narrative/

The smart narrative tool looks at trends and key influencers, but if you want to be in charge of the text you want to display on a report, you will have to write the DAX to get it.

In the example below a line chart displays sales profit by customer segment and the chart is filtered by a slicer to display customers in U.S.A.

Under the chart a text box is added, and by DAX the text box displays useful information for the report audience.

The text in the text box is dynamic and will change by all filtering on this page.

Below, Canada is selected from the slicer. You can see that the dynamic text now has changed to show Canada details.

A year slicer has been added to the report (The interaction between the year slicer and the line chart has been deactivated), and the text box’s information have been changed again to only display the selected year.

And now to the DAX part.

It may look a bit advanced.

A measure has been created see following DAX:

Country text =

” The line chart above shows Profit for ” & VALUES ( Customers[Country] ) & ” over years by customer segment. The Sales growth for ”

& VALUES ( Customers[Country] ) & ” is ”

& FORMAT ( [Growth], “0.00” ) & “%, and the bestselling product in ”

& VALUES ( Customers[Country] ) & ” is ”

& CONCATENATEX (

TOPN ( 1, VALUES ( Items[Product Description] ), [Sales] ),

[Product Description],

“;”

) & “. At the moment we have ”

& COUNT ( Customers[Customer Id] ) & ” customers in ”

& VALUES ( Customers[Country] ) & ” and the average order sales is ”

& FORMAT ( DIVIDE ( [Sales], COUNTROWS ( Orders ), 0 ), “currency”, “en-gb” ) & “, and average quantity per order is ”

& FORMAT (

DIVIDE ( SUM ( Line_Items[Quantity] ), COUNTROWS ( orders ), 0 ),

“0,00”

) & ” units.”

In the DAX you will find a combination of other DAX measures, text, and DAX functions all concatenated by & (ampersand). And all free text surrounded by “” (quotation marks).

To make it simpler I will break down the DAX in smaller pieces.

Part 1:

Country text =

” The line chart above shows Profit for ” & VALUES ( Customers[Country] ) & ” over years by customer segment. The Sales growth for ”

& VALUES ( Customers[Country] ) & ” is ”

The VALUES ( Customers[Country] ) return the value selected from the slicer, so if Canada has been selected from the slicer, the first part will return

“The line chart above shows Profit for Canada over years by customer segment. The Sales growth for Canada is”

Part 2:

& FORMAT ( [Growth], “0.00” ) & “%, and the bestselling product in ”

& VALUES ( Customers[Country] ) & ” is ”

In my example model I have used a time intelligence DAX function SAMEPERIODLASTYEAR to be able to calculate the growth.

Last year = CALCULATE([Sales],SAMEPERIODLASTYEAR(Dates[Dates]))

(If you want more insight about time intelligence DAX function, follow the link below:

https://www.stl-training.co.uk/b/power-bi-mysterious-calculate-function-3-time-intelligence/)

Then the Growth measure looks like this:

Growth = divide([Sales]-[Last year],[Last year],0)

To get it formatted you can use the Format function. In this example with two decimals and the % sign.

Part 2 if Canada is selected from the slicer will return:

“3.88%, and the bestselling product in Canada is”

Part 3:

& CONCATENATEX (

TOPN ( 1, VALUES ( Items[Product Description] ), [Sales] ),

[Product Description],

“;”

) & “. At the moment we have ”

The CONCATENATEX function is used to extract the product name. The TOPN function is used to find the top 1 best selling product by the sales.

Part 3 if Canada is selected from the slicer will return:

“cross – country ski. AT the moment we have”

(cross = country ski is number 1 selling product in Canada in the data set used here)

Part 4:

& COUNT ( Customers[Customer Id] ) & ” customers in ”

& VALUES ( Customers[Country] ) & ” and the average order sales is ”

& FORMAT ( DIVIDE ( [Sales], COUNTROWS ( Orders ), 0 ), “currency”, “en-gb” ) & “, and average quantity per order is ”

The number of customers in Canada can be displayed in the text string by count the number of customer IDs.

And to return the average sales per order can be done by dividing the sales by number of orders. In the example data set a table store a record for each order in an Orders table.

The FORMAT function is here used to return currency formatting.

Part 4 if Canada is selected from the slicer will return:

“8 customers in Canada and the average sales order is £23,854.62, and average quantity per order is ”

Part 5:

& FORMAT (

DIVIDE ( SUM ( Line_Items[Quantity] ), COUNTROWS ( orders ), 0 ),

“0,00”

) & ” units.”

Again, the FORMAT function is used, this time to get 0 decimals, and the DIVIDE function to get the average quantity per order.

Part 5 if Canada is selected from the slicer will return:

“489 units.”

The whole text string if Canada is selected from the slicer will return:

“The line chart above shows Profit for Canada over years by customer segment. The Sales growth for Canada is 3.88%, and the bestselling product in Canada is cross – country ski. At the moment we have 8 customers in Canada and the average order sales is £23,854.62, and average quantity per order is 489 units.”

Last step is to add the dynamic text measure to a text box.

Add a Text box to the page and turn on Title in the Format pane to the right.

Next to the Text input box press the fx button.

Click the drop-down arrow next to the “What field should we base this on?” navigate, find the measure, select it, and click OK bottom right.

Conclusion

You can add much more information to your reports by creating dynamic text boxes. They allow you to be able to explain the visuals, you can point out specific details, which are important for the audience.

You can find more examples like this by following STL’s LinkedIn page. Dynamic text can be used for much more than just creating text for text boxes.

DAX can be complicated and challenging. If it is not done right, DAX can be particularly difficult to debug and can slow down the underlying queries. In other words, you do not know if you have the right results. This means the audience of your reports could be sitting and waiting for the output when they filter the report.

Often complex calculations require us writing compound or complex expressions. Compound expressions can involve the use of many nested functions, and possibly the reuse of expression logic and reuse measures multiple times. All of this will add to the time required by the underlaying queries to display the result.

Using variables in your DAX formulas can help you write more complex and efficient calculations. Variables can improve performance and consistency and make the measures easier to read and understand.

In this blog post, you will see demonstrations of some of the benefits, you will achieve by using variables.

In the example you will see the benefits in a year over year profit growth % measure. The math is:

Current year sales minus sales cost minus last year sales minus sales cost divided with last year sales minus sales cost.

To get the values last year you will need to use a time intelligence function.

(You can click the link here to get more insight about time intelligence: https://www.stl-training.co.uk/b/power-bi-mysterious-calculate-function-3-time-intelligence/)

The measure without variables will look like below.

Profit Growth yoy % =

DIVIDE (

( [Sales] – [Sales cost] )

– (

CALCULATE ( [Sales], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

– CALCULATE ( [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

CALCULATE ( [Sales], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

– CALCULATE ( [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) ),

)

And could be visualised on a report page like this in a matrix visual:

DAX

Improve performance with DAX

Notice in the DAX above that there are many repetitions. The CALCULATE function is used four times, the PARALLELPRIOD function four times, and the sales and sales cost measure been referenced several times.

This will not only make it complex to read, but will also slow down the query, by asking the query to do the same calculations several times. The measure definition can be made more efficient by using variables.

How to create a variable

You create a variable by typing VAR and then you need to name the variable. After the name of the variable, you need to tell, what you want to store in the variable. To get the result you want to visualise you use the word return.

Below you can see the first variable called profit. This variable will now calculate the profit and store the profit.

Profit Growth yoy % =

VAR profit = [Sales] – [Sales cost]

VAR LastYear =

CALCULATE ( [Sales] – [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

VAR DiffCurLast = profit – LastYear

VAR Result =

DIVIDE ( DiffCurLast, LastYear, 0 )

RETURN

Result

We have used 4 variables to get the result. 3 variables for the 3 calculations to get to the Result, which here is stored in a variable named Result. The word RETURN is used to explain the measure what to return from the measure. Above the value in the Result variable needed to be visualised.

The measure continues to produce the correct result and does so in about half the query time.

Improve readability

In the previous measure definition, notice how the choice of variable name makes the RETURN expression simpler to understand. The expression is short and self-describing.

Simplify debugging

Variables can also help you debug a formula. To test an expression assigned to a variable, you temporarily rewrite the RETURN expression to output the variable.

By changing the DAX RETURN to DiffCurLast

Profit Growth yoy % =

VAR profit = [Sales] – [Sales cost]

VAR LastYear =

CALCULATE ( [Sales] – [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

VAR DiffCurLast = profit – LastYear

VAR Result =

DIVIDE ( DiffCurLast, LastYear, 0 )

RETURN

DiffCurLast

The matrix will now display the difference value not the in percentage.

And by changing the RETURN to profit you debug the profit = [Sales] – [Sales cost]

Profit Growth yoy % =

VAR profit = [Sales] – [Sales cost]

VAR LastYear =

CALCULATE ( [Sales] – [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

VAR DiffCurLast = profit – LastYear

VAR Result =

DIVIDE ( DiffCurLast, LastYear, 0 )

RETURN

Profit

You can debug all the variables to make sure that each part of the measure returns the right result

Conclusion

Variables in DAX measure can reduce the execution time for the underlaying queries. Make DAX measures easier to read and understand, and much easier to debug.

You can learn about variables on STL’s one day DAX course, if you want to get insight about variables in DAX measures.