This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	DAX (59 exercises)
Topic ==>	Calendars and dates (8 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Import and configure a custom calendar table in Power BI Desktop

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Open the Music Tours - Custom Calendars.pbix file in the folder shown above. In this report we've disabled the automatic calendar feature.

The lack of a calendar icon next to date fields is a good indicator that the automatic calendar feature is disabled.

Import the Show calendar worksheet from the Excel file called Show calendar.xlsx.

Importing an Excel worksheet in Power BI Desktop

Import this worksheet.

Mark the newly imported table as a date table, using the Date column as the key.

The Date column should display this icon when you've done this.

Set the default summarization of any number columns in the table to Don't summarize.

A list of fields in a data table in Power BI Desktop.

Power BI tries to sum values in numeric columns, like the Day column shown here. We don't want this to happen!

Change the Sort by setting of the columns shown in the table below:

Column	Column to sort by
Month	Month number
Weekday	Weekday number

Create a hierarchy starting with the Decade column. Rename the hierarchy as Show date hierarchy and add the fields shown below to it:

A hierarchy in a data table in Power BI Desktop

You can create a hierarchy in the Model view of the report.

Change the visibility of columns in the table according the diagram shown below:

A data table in the Model view in Power BI Desktop

Hide the columns used for sorting and any columns which also appear in the hierarchy.

Create a relationship between the Show calendar and Show tables.

A one to many relationship between data tables in Power BI Desktop

Create the relationship between the columns highlighted here.

Use your new calendar table to create the following chart visuals:

Column chart visuals in Power BI Desktop

You'll find some measures in the Music Measures table to help you.

Add a slicer visual which you can use to filter the charts by the Weekday field.

A slicer visual in Power BI Desktop filtering a column chart by wekkday

Use the slicer to filter the charts.

Save and close the report.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Visual C# training

Excel training courses

Power Automate training

Power BI / DAX training

Python training courses

SQL training courses

VBA (macros) training

Site map

Terms & Conditions

EXERCISE TOPIC

POWER BI EXERCISES

DAX EXERCISES

Import and configure a custom calendar table in Power BI Desktop

Head office

London

Manchester