We live in a fast-paced environment. Stakeholders are pressed for time and have little patience. It could be difficult for them to move from one report to the next. As a result, the importance of having all aesthetically appealing reports on a single reporting dashboard cannot be overstated.

We explored some DAX functions in this Data Digest episode to address the challenge of tracking multiple KPIs from within a single dashboard. This technique keeps your dashboard simple - there's no need for multiple reports for different KPIs, and easily makes it more appealing and sticky to your stakeholders.

Instructor: David Obeh Alobo (LinkedIn)

If you prefer to follow along with a written tutorial, we've also got you covered.

We'll assume you already know your way around Power Query, DAX, Data Modelling, and Power BI in general.

Some of the DAX functions employed include:

1. SWITCH - See documentation
2. SELECTEDVALUE - See documentation
3. IF - See documentation
4. SUM - See documentation
5. ISBLANK - See documentation
6. VAR and RETURN - See documentation
7. Blank - See documentation

At the end of this short tutorial, you should be able to create the following:

• Dynamic headers with DAX
• Dynamic Background Colour with DAX
• Dynamic Measures
• Dynamic Visual Header with DAX
• KPI Visual with Disconnected Tables
• Dynamic Comment

Resources: 📁 Download dataset.

Data preparation process

We have imported the data and created the data model as shown below:

From the model tab, create a new table, My_Measures:

My_Measures =
{BLANK()}

This table would serve as a folder or container to house all measures for proper housekeeping.

In this table, create the following measures:

Profit = SUM(Orders[Profit])

Quantity Ordered = SUM(Orders[Quantity])

Sales = SUM(Orders[Sales])

Cost = [Profit] + [Sales]

Dynamic background

Create a measure, Background:

Background = SWITCH(
SELECTEDVALUE('Location'[Region]),
"East", "white",
"West", "light gray",
"South", "silver",
"Pink"
)

1. From your insert Tab, in the Element Group, click on shapes and select Rectangle
2. Fit to size as shown in the image below
3. From the Format Shape Pane, click on style and click on ‘fx’
4. In the format style field, change it from Gradient to Field value
5. In the ‘What Field Should we base this on?’, select the measure you created called Background and click OK.
6. Remove the border of the image
7. Format Tab, select ‘send to back’

Dynamic header

1. Copy and paste the shape you just created and fit to position and ensure there is a white space between the shape above and below as shown below
2. From Format Shape Pane (style section), change the fill colour to #6b2328 or any colour you desire
3. Create a measure, Header;

Header =
IF (
ISBLANK ( SELECTEDVALUE ( 'Date'[Year] ) ),
" ",
SELECTEDVALUE ( 'Date'[Year] )
) & " "
& SELECTEDVALUE ( 'Measure Table'[Measure] ) & " Breakdown for "
& IF (
ISBLANK ( SELECTEDVALUE ( Location[Region] ) ),
"All Regions",
SELECTEDVALUE ( Location[Region] )
)

1. From Home Tab, Select Text Box
2. In the Format Text Box Pane, Title, toggle ON
3. Click on ‘fx’ in the Text Field and select the measure, Header, as we demonstrated for background colour earlier.
4. Change the font colour to white and increase the font size to 28px
5. Remove the text box background by clicking on Effects in General Tab and toggle the background button OFF.

Creating Slicer

1. From the Visualization pane, select slicer
2. Select Region in Location Table from the Fields Pane
3. Click on Format and toggle background button OFF
4. Change slicer orientation to Horizontal in the slicer settings
5. Toggle Single Select Button OFF from selection and toggle multiple select button ON
6. Make the font colour black
7. Make border colour grey, the line weight 4px and background colour white
8. Create another slicer for Year

Dynamic measures

Dynamic Measure =
VAR Selection =
SELECTEDVALUE ( 'Measure Table'[Measure], "Sales" )
RETURN

SWITCH (
TRUE (),
Selection = "Sales", [Sales],
Selection = "Cost", [Cost],
Selection = "Profit", [Profit],
Selection = "Quantity Ordered", [Quantity Ordered],
"Sales"
)

Creating the Visuals

1. Insert rectangle shape, make rounder corners 11px
2. Create a measure, Inner-Background;

Inner-Background =

SWITCH (

SELECTEDVALUE ( 'Location'[Region] ),

"East", "white",

"West", "light gray",

"South", "silver",

"Pink"

)

1. Follow the step for changing background as discussed earlier to change the background colour. But in this case, the measure to select would be Inner-Background
2. From the Format Pane, select General, click on effects toggle shadow button ON (Colour: Black, Offset: White, Position: Bottom Right)
3. Toggle Visual Border Button ON (Colour: Grey, Rounded Corners: 4px)

Dynamic visual header

Create a measure, Dynamic Measure

Dynamic Measure =

VAR Selection =

SELECTEDVALUE ( 'Measure Table'[Measure], "Sales" )

RETURN

SWITCH (

TRUE (),

Selection = "Sales", [Sales],

Selection = "Cost", [Cost],

Selection = "Profit", [Profit],

Selection = "Quantity Ordered", [Quantity Ordered],

"Sales"

)

1. Select stack column chart from the visualization pane
2. Remove background and format as shown below
3. Create Dynamic Measure against Category
4. Repeat same step for other visuals: Dynamic Measures by Sales Rep, Dynamic Measure by Product, and Dynamic Measure by Year and Month.

KPI visual with disconnected tables

Disconnected Tables are those tables in our models where we don’t have any relationship with them. In other words, any model table that is not related to any other model table. Basically, it is used to accept “user input”, allowing model calculations to use the input value in a meaningful way. It is not intended to propagate filters to other model tables.

1. Create a disconnected table as shown in the diagram below
2. Select Slicer and drag the KPI column to the field
3. From the Visual Tab, click on selection and toggle the single select button ON
4. Click on option and toggle it OFF, and make the orientation Vertical
5. Create a Measure, KPI Title

KPI Title =

SELECTEDVALUE ( Location[Region] ) & " Total "

& IF (

ISBLANK ( SELECTEDVALUE ( 'Measure Table'[Measure] ) ),

" All Region ",

SELECTEDVALUE ( 'Measure Table'[Measure] )

) & " "

& IF (

ISBLANK ( SELECTEDVALUE ( 'Date'[Year] ) ),

" All Year ",

SELECTEDVALUE ( 'Date'[Year] )

)

1. Select a card visual and drag Dynamic Measure to the field
2. Go to general in the Format Pane, toggle title ON, click on fx and select KPI Title as we illustrated for background colour.

We do hope that you found this episode of the Data digest series exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!