0845 643 64 63

Monthly Archives: November 2021

Azure Storage Backup Retention

This blog is a follow up to a previous blog I wrote about backing up Azure Analysis Services cubes in Azure, that blog can be found here. This blog shows how to implement a retention policy using PowerShell in Azure Runbooks to remove the backups after a set number of days. To create a new Runbook in the Azure portal, go to the relevant Automation account in the relevant resource group and then select Runbooks from the left hand pane. Note you will need to add the Az.Storage module to the automation account to be able to use some of the commands listed in this blog.

The parameters required here are shown below, where ResourceGroupName is the name of the resource group that has the relevant storage account, StorageAccountName is the name of the storage account resource and ContainerName is the name of the container holding the backup files to be removed. BlobName is part of the name of the files and will be used later to obtain only the relevant files from the storage container. RetentionDays specifies the number of days to keep a backup before it is removed.

The command Set-AzCurrentStorageAccount sets the storage account to be used for the subsequent commands, which gets the container using the ContainerName parameter and then lists the relevant files in the blob container using the BlobName parameter.

Once the list of backup files has been obtained, each file can be checked in turn and removed if it meets the relevant criteria. For each file obtained, the LastModified property is used to identify when the file was created. If the resulting date time is older than the number of retention days from today, the Remove-AzStorageBlob command is used to remove that file from the storage container. This check is repeated for all files, removing the relevant files, and leaving only those that are not older than the RetentionDays parameter.

This is all you need to start cleaning up the backup files in Azure, and although I have used Azure Analysis Services backups for my example here, this code can be used for any blob files in Azure.

How to create a toggle button in Power BI

In this blog post I will be showing you how to use a toggle button in Power BI. A toggle button in Power BI allows users to quickly switch between two options.

Our first step is to create a toggle button as Power BI doesn’t have one by default. You can import one but by creating it yourself, it gives you more control.

Our toggle button is created by having two images of identical size over the top of each other and using bookmarks to flip between the two. Therefore, you can create your images using any software you like. I’ll be using PowerPoint and importing shapes.

In PowerPoint, import the Flowchart: Terminator shape.

Then, import an oval and shape it into a circle. Change the shape fill to white or grey and move the circle so that it fits inside the curve of the curved rectangle.

Once that is done, copy and paste the shapes that form the toggle button and move the white circle to the other side of the curved rectangle. Next, import a textbox over the shapes and include the text you would like for the toggles.

My Power BI report is going to toggle world record times between Men and Women, so my toggles look like this:

It is crucial that these images are the same size, therefore we need to make sure there is no whitespace around the toggle images. To ensure this. I copy the two images into everyone’s favourite image editor – Microsoft Paint! There, crop all the whitespace around the shapes before saving the images as .png files.

It is now time to import these two created images into Power BI and align them such that they’re directly on top of each other. This is required because the image’s visibility will be toggled to give the impression it’s one button instead of two images. You can ensure the images are exactly aligned using the position and size properties.

Toggling the visibility of the images and associated charts is done using bookmarks. The below screenshot shows for the Women’s bookmark: all men related items are hidden and all women related items are shown.

The final step is to action the images such that when clicked on, the bookmark is activated and toggles to the other image and charts. When the women’s toggle is clicked activate the men’s bookmark.

Hopefully you should end up with something like this:

Clicking on the toggle in the top right corner toggles us to the women’s chart.

Clicking on the toggle in the top right corner again takes us back to the men’s chart.

This is my 2nd blog post that uses PowerPoint to enhance my Power BI experience, please see the first blog post here: https://www.purplefrogsystems.com/blog/2019/12/creating-power-bi-layouts-using-powerpoint/

Change the Compatibility Level of a Power BI report

A number of new features in Power BI are limited to newer Power BI datasets, which Microsoft track through the use of Compatibility Levels.

  • 1500+ for SQL Server 2019 compatibility
  • 1400+ for SQL Server 2017 compatibility
  • 1200+ for SQL Server 2016 compatibility

If you want to, for example, connect to the xmla endpoint of the dataset from SQL Server Management Studio, then you need level 1500 or greater.

If it’s lower you’ll get the following error message when you perform some operations:

The database being created has a compatibility level ‘1465’, which is not supported by the XMLA endpoint in Power BI Premium. Please use a compatibility level that is at least 1500.

How do you change this level?

Tabular Editor to the rescue! (Download Here)

With Tabular Model Editor you can connect to your dataset in PowerBI.com, click on the ‘Model’ in the left tree, then in the properties window expand ‘Database’, change Compatibility Level to 1500, then File -> Save.

Note: to connect to your Power BI dataset you need to know the server name, which you can get from the Power BI portal. Go to the workspace, in list mode. Click the elipses next to the dataset, and select ‘Settings’.

From here, expand Server Settings, and copy the connection string.

</Frog Blog-Out>

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out