0845 643 64 63

Uncategorized

Deploy Azure AutoML Model And Consume In Excel

It has come back to my turn to write a blog post, and if you remember my previous one concerned why you should use Azure based AutoMl and subsequently how to do so. If you followed that then you will be left with a model of which you’ve scored and know the performance of, but no way of how to then deploy and use your model. I will outline the steps needed to do this (which involves a major shortcut as we are using an AutoMl model), and then show you the required VBA needed to consume this in Microsoft Excel.

Create an endpoint

The first step is to navigate to your selected model in Azure Machine Learning Studio, click the dropdown on the deploy tab and select deploy to a web service.

You will then be met by the below screen which is where we really benefit from using an AutoMl model! If this was not an AutoMl model, we would have two other fields here asking for an entry script file and a Conda dependencies file. The point of these being to allow whatever is calling the model to know how to use it and also what packages it needs to run it. Luckily, AutoMl has already done this for you, so all you need to do is give the deployment a name (no spaces, special characters or spaces allowed) and then for ease of use go for an Azure Container Instance for Compute type. Then click deploy!

Then navigate to the ribbon on the left of your screen and select endpoints to see your end point. Open this and you will see it will have a deployment state of ‘Transitioning’ for a while and when this is ‘Healthy’ you are ready to go. When ready, you will see a few tabs at the top of the page (under the deployments name) the main ones are ‘Test’ where you can manually enter values into a JSON format to get a result, and ‘Consume’ where you can see the REST endpoint you have created as well as useful code snippets on how you can implement your model in C#, Python and R; but not Excel which will be more familiar to your average data consumer.

How to consume in Excel

Before we write any VBA we need to make a table in a spreadsheet for the data to be entered into and results to outputted to. If you go back to the ‘Test’ tab, it’s useful to look at the JSON to see what order the features will be read as they must match the order shown in the JSON.

You can then create a table which mirrors this.

You then need to write some VBA to link these elements up, so you firstly have to make a VBA module. To do this you need to open the VBA GUI which is accessed via the developer tab and clicking ‘Visual Basic’.

You then need to click the VBA project with the name of your excel workbook , then click the insert tab and select ‘Module’. This is where you will write your VBA which we will do in two sections. The first being a function which performs the HTTP request to the end point. We want it to take our Features and the REST endpoint and make a HTTP request with these and store the response. The second will be a sub that needs to create the JSON we saw in the ‘Test’ tab earlier, as this is how the endpoint needs the input data packaged, and then give this to your function to send to your model.

Function RunScore(Features, RestEndpoint)

    If WEBURL <> "" Then
        Url = RestEndpoint
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        objHTTP.Open "POST", Url, False
        objHTTP.setRequestHeader "Content-type", "application/json"
        objHTTP.send (Features)
        RunScore = objHTTP.ResponseText
    '    MsgBox (resp)
    Else
     MsgBox "Not WEB URL Provided"
    End If
    
End Function
Sub Score()
    
    On Error GoTo Error:
    
    Range("B2").Select
    NumRows = Range(Selection, Selection.End(xlDown)).Rows.Count
    
    For r = 2 To 2 + NumRows
    
    
        body = "{" & Chr(34) & "Inputs" & Chr(34) & ":{" & Chr(34) & "data" &            Chr(34) & ": [[" & Range("B" & r) & "," & Range("C" & r) & "," & Range("D" & r) & "," & Range("E" & r) & "]]}," & Chr(34) & "GlobalParameters" & Chr(34) & ": 1.0}"
    
        WEBURL = http://999def99-9e9d-999e-b99a-aa1c12820857.uksouth.azurecontainer.io/score
        
       
        
        ret = RunScore(Features, RestEndpoint)
        
        openPos = InStr(ret, "[")
        closePos = InStr(ret, "]")
        midbit = Mid(ret, openPos + 1, closePos - openPos - 1)
        midbit = Round(midbit, 2)
        
        Range("F" & r) = midbit
        
    Next r


Exit Sub
Error:
      MsgBox (Err.Description)
End Sub

Ignoring anything that mentions error (this is just a wrapper that tells you what’s gone wrong, if something does go wrong), we are selecting the first cell where there should be data and then finding out how many rows of data there are. We then loop through the rows creating the JSON for each row, this is stored in body. It is essential that you use the string exactly as it is above, only editing the cell locations to match the length and order of your data, the features must be in the order that they are shown in the JSON we looked at earlier. Along with storing the Rest Endpoint in a variable, we then call the function we made earlier, the output of which we have to do a bit of manipulation on as it will be wrapped in brackets etc. We then place the value we have extracted into the result location we created in the spreadsheet earlier.

We now need a button in the spreadsheet to allow us to run the code ‘at the click of a button’ to excuse the pun. This can be done via the developer tab in excel where you need to click insert and select a button. The button needs to be placed via a click and drag action in the spreadsheet exactly like how you insert shapes in excel. Upon releasing your mouse button a menu will pop up listing the macros you have in the spreadsheet, select ‘Score’ (or however you have named it), which will assign the macro to this button.

It is now ready to go, you can insert the features you want values for (on a row by row basis) and when you have entered the features in the table, you simply click the button we created and the model will be run and you will see the results outputted in your table!

The SQL Query Alias Conundrum – Order of Execution

Falling Dominoes

So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘.

The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it?

If you’ve ever written a SQL query, this should look familiar:

  1. SELECT
  2. DISTINCT
  3. FROM
  4. JOIN
  5. ON
  6. WHERE
  7. GROUP BY
  8. HAVING
  9. ORDER BY

It makes sense that we read the query from top to bottom / left to right (especially for those in the West), but contrary to how it may look, this isn’t how the query is executed. The order of execution is:

  1. FROM / JOIN
  2. WHERE / ON
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. DISTINCT
  7. ORDER BY

When you stop and think about what is happening, logically, this order makes sense.

FROM / JOIN – First and foremost, we need to determine the location of the data set that we are querying.

WHERE / ON – We need to narrow that data set down with predicates to keep only the records we care about.

GROUP BY – If you need to aggregate the results, this is done next to give us an even more concise result set.

HAVING – WHERE 2.0! This is a predicate over the aggregated result set.

SELECT – Okay, now we have our final result set, containing ONLY our required rows. Now, finally, we can select which columns we want from that dataset.

DISTINCT – Now we know which columns we want from the result set, we can strip out any duplicates.

ORDER BY – Finally, we have whittled the result set down to only the rows and columns we need, so we can order it however we want.

Question Mark

What is the value in knowing this?

It’s important to understand the difference between the logical and lexical order of a query. Knowing this will help you troubleshoot any syntax errors you may encounter.

Going back to our original issue of using an alias in a WHERE clause, you can now see why you get an error about an invalid column.

Look back at the order of execution, aliases are assigned to columns during the SELECT stage, WHERE comes before SELECT. The engine is trying to process the column by its alias before it’s been assigned. ORDER BY comes after SELECT so the same issue doesn’t arise here

It also explains why Intellisense doesn’t autocomplete or suggest column names until you’ve added the FROM statement. Until you tell the engine where the data is coming FROM, it doesn’t know which columns it can SELECT.

Download Power BI Reports using PowerShell

In this blog post I will be sharing a PowerShell script that allows multiple Power BI reports to be downloaded at once.

In the Power BI service, there is no way of downloading multiple Power BI reports at once. Therefore, users must download files one by one which is slow, time consuming and inefficient. Thankfully, there are ways around this, one of which is using PowerShell.

The script is downloadable at the bottom of the page as a txt file. To use the script there are three steps to take.

The first step is to install and import the MicrosoftPowerBIMgmt PowerShell module. The following code should do the trick.

The second step is to fill in the variables at the top of the script:

  • Workspace Name
  • Report Name
  • Download Path

Workspace Name – The name of the workspace in which the report or reports are stored.

Report Name – This can be configured to download one, multiple or all reports in the workspace.
One report = @(‘Report 1’)
Multiple reports = @(‘Report 1’, ‘Report 2’, ‘Report 3’)
All reports = @()

Download Path – The folder where the report or reports will be saved to.

Once these variables are assigned the script is ready to be executed.

The third and final step is to sign into Power BI, a prompt asking you to sign into Microsoft Azure will pop up. Use the credentials you would use to sign into PowerBI.com.

The script should now run in full and bring the PBIX files into the designated folder.

The script is attached as a txt file.


This script will only save your Power BI reports if they can be downloaded as PBIX files. If Large dataset storage format or Incremental refresh has been enabled the reports can’t be downloaded as a PBIX file. To back these reports up I would recommend Power BI Sentinel – https://www.powerbisentinel.com/

How to parameterise the Execute Pipeline activity in Azure Synapse Analytics.

Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run.

However, with the use of a Microsoft API there is method which we can use to overcome this.

In this blog post we’ll use the “Pipeline – Create Pipeline Run” API documented below:

https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run

Using this API will allow us to dynamically pass in a pipeline name and trigger it. It will also us to dynamically pass in parameters to the called pipelines.

For this example, we’ll create 3 simple pipelines:

  1. “Wait 5 Seconds”
  2. “Wait 10 Seconds”
  3. “Wait 15 Seconds”

Plus a “Control Pipeline” that we’ll get onto later.

Each of the “Wait” pipelines contain a single “Wait” activity and a parameter called “WaitDuration” with a “Type” of “Int”. The idea being that we will dynamically pass in a wait duration from an array.

The “wait time in seconds” option in the settings tab of the “Wait” activity is then parameterised to refer to the parameter as shown below.

Next, we’ll build the “Control Pipeline” based on a “For Each” activity which will loop around the array displayed in notepad++ below. This array references the Pipeline Name that we want to dynamically trigger plus the Wait Duration we want it to wait for.

To use this array we’ll create a “ParameterArray” parameter with “Type” equal to “Array” in the “Control Pipeline”. We’ll set the default value equal to the array from above.

Next, within the settings tab of the “ForEach” activity we have the option of ticking the sequential option and listing the items we want to loop over.

Here we’ve got the “Sequential” option ticked. This just means that it will loop through each item in our array, starting with the very first item. For the “Items” option we’ll add the dynamic content which refers to our “ParameterArray” parameter.

Next, within the “ForEach” activity we’ll create a “Web” activity with the following options.

URL:

https://{Your Synapse Workspace Name}.dev.azuresynapse.net/pipelines/@{item().PipelineName}/createRun?api-version=2020-12-01

For example:

https://mytestsynapseworkspace.dev.azuresynapse.net/pipelines/@{item().PipelineName}/createRun?api-version=2020-12-01

*Notice here that I’ve added @{item().PipelineName} into the string which is our pipeline name from our “ParameterArray” parameter. This is what will make our Web API POST request dynamic.

Method:

POST

Headers:

Name : Content-Type

Value : application/json

Body:

{“WaitDuration”:@{item().WaitDuration}}

*Also notice here that i’ve added @{item().WaitDuration} into the string which is our wait duration from our “ParameterArray” parameter for each item. This allows us to feed each of the pipelines with the wait duration.

Integration runtime:

AutoResolveIntegrationRuntime

Authentication:

System Assigned Managed Identity

Resource:

https://dev.azuresynapse.net/

Once we’ve entered all of these details we should have a “Web” activity which looks similar to the image below:

Now let’s trigger our “Control Pipeline”:

As we can see each our 3 pipelines have been triggered by just triggering the “Control Pipeline”. We can also see that the respective parameters have been passed in successfully to each of the triggered pipelines.

Wait 5 Seconds:

Wait 10 Seconds:

Wait 15 Seconds:

Note, if you’re using Azure DevOps Git CI/CD integration you’ll need to make sure that the pipelines you want to execute already exist in live mode for the API request to recognise that they exist. If not you’ll get an error code suggesting that the pipeline doesn’t exist.

Also note that there may be some Azure permissions that you’ll need to configure with your Azure Administrator in the Azure portal to allow this to work.

In summary, this method is a trigger and forget approach. In this blog there is no consideration in the for each activity to wait for the pipeline to either succeed, fail or timeout. However with the use of the “Pipeline Runs – Get” API and a “Until” activity you can easily make this work.

AutoML; How to use it and why

Introduction

Some of you may have noticed that the Purple Frog team were at the largest community-led SQL Server and Microsoft Data Platform conference in Europe last week; SQL Bits. With all of us learning a lot, I thought I would share some of the knowledge I picked up in the Machine Learning space with you this week. I went to a session by Gavita Regunath of Advancing Analytics on the topic of AutoML, and why not just beginners in the ML space should be using it, but also seasoned pros as a way to get a jump start on model creation and prototyping. In this blog, I will walk you through how to set up and run an AutoML experiment in the Machine Learning Studio of Azure Synapse (although there are many other AutoML options out there) and then briefly summarize the benefits for a beginner and the more experienced.

What is AutoML?

It is what it says on the tin; it’s automatic machine learning. In essence you give the tool the dataset you are using, the kind of question you are asking of that dataset (for Azure Synapse this is classification, regression or time series forecasting) and then the parameter you are interested in. You then run the tool, which will do any pre-processing it deems fit (imputation, scaling etc) and run the data through as many models as it can in the time frame you have given it. When complete it will show you the best model it found with the metrics it used as well as the hyperparameters it used.

How to use AutoML

We shall be working on the presumption that you are already a user of Azure Synapse, so the first step will be to set up a Azure Machine Learning Studio resource. To do this, navigate to the home screen on your Azure Synapse subscription and click ‘Create a resource’.

Then search for Machine Learning and then click the above shown icon which will bring you to the above screen, where you will then want to click create and fill in the following fields. A lot of the fields will already have the necessary components if you are already using Azure Synapse. If not, creating them is straight forward with the hyperlinks below the boxes. Once these are filled in you then need to click ‘Review + create’ and then wait till the Machine Learning resource has been created.

Next, navigate to your resource and launch the studio. We are almost at a point where we can start doing some AutoML! However, we need to create a compute instance to run our AutoML.

You will be met by the home screen of the Machine Learning Studio, and the first thing we need to do is navigate to the manage ribbon and select compute; then new.

For the next step you will be met with the below screen, and I would recommend the following settings (the compute name can be whatever you want as long as its unique). Additionally, you would only need a GPU virtual machine type if you were intending on performing some deep learning, which we will not cover in this blog post.

We then wait till that’s created and you go back to the studio home screen. You then want to select ‘Start now’ on the ‘Automated ML’ square. Then select ‘New Automated ML run’

Next, you will see the below screen. For a first run you can use one of the built in datasets. To do this, click ‘Create dataset’, then ‘from open datasets’.

I will be showing you how to solve a classification problem, and the Diabetes dataset will be the most conducive for this (although peculiarly it doesn’t state if you have diabetes or not, so we will be predicting sex); so select this dataset, press next, give it a name and click create.

You will then be taken back to the screen where we chose to create a dataset, and where we can continue to set up the AutoML experiment. Put a tick next to the dataset you have just set up, and then click next to go to the ‘configure run’ screen.

You will then have the above to be filled out. The target column is what we are trying to predict and in our experiment we are using a compute instance, of which you then need to select the one you created earlier. Upon clicking next you simply want to just select that you will be dealing with a classification problem and on the additional configuration settings change the training job time to 0.5 hours (to save time and resource). On the next screen keep everything as default for this run.

Click finish and the experiment will start. Wait until the experiment completes, then you will be met with the below screen with an output of the best model found.

We see that a voting ensemble algorithm was settled upon with an AUC of 0.7655, which is quite good. If we click on voting ensemble we can get more details (of which I will go through a couple of key points). Firstly, we can click ‘view all other metrics’ to get a holistic view of model performance.

Under the explanations tab you can see the feature importance (I have limited it to the top 4 features).

Finally, in the model tab you can get the details of the model in terms of pre-processing and hyperparameter tuning. In regard to our voting ensemble algorithm that was settled on, we have model details for each individual model which makes up the ensemble.

Why should beginners use AutoML

As I’ve shown above, the actual process of creating a model using AutoML requires no knowledge of machine learning to get what can be a respectable and viable model, which you can also deploy from the same environment you created it in. The ability to read into what hyper parameter settings have been used as well as pre-processing allows you to analyze what the tool has done and learn from this.

Why should experienced people use AutoML

In short, it can save you a lot of time and give you a head start in your model creation process. For example, in this blog I have shown that in the space of half an hour the AutoML tool tried 51 different model, hyperparameter and pre processing combinations to arrive at its best model. This is something even the most seasoned data scientist would take a lot longer to do. In addition to this, as it provides you with info on the final model, you can take this away and further refine it to your specific needs.

Summary

In closing, I hope I have given you a solid guide in how to use the AutoML capabilities that Azure Synapse provides. I also hope I have convinced you that AutoML in general is a tool that can and should be used by all regardless of ability and knowledge of ML.

Combining Queries from Multiple Sources in Power BI using Merge and Append

It is always good practice to do as much data preparation as close to the sources as you can before importing or connecting them to your Power BI reports, but what if there are circumstances where this isn’t possible?

I had an issue recently where a third-party application had been updated and both the new and legacy versions were being used side-by-side. Logging data from both versions was being written to two separate Azure SQL databases.

The customer needed a Power BI report showing both old and new logging data sets as a single source. If both databases were SQL Server databases, I could have written a view with a cross-database join, imported that into Power BI and thought no more about it. However, the two sources being Azure SQL Databases, with no easy way to join the tables, caused an issue.

This is where the Merge and Append functions in Power Query come in.

The first step to solve my issue was to create a view in each of the Azure databases that were identical in structure (you’ll see why later on) and import these into Power BI.

Now these data sources (referred to as ‘Queries’ by Power BI) have both been imported into the data model, we have two options with regards to how to combine them, ‘Append’ and ‘Merge’.

Merge
Although I didn’t use the ‘Merge’ function, I have included some information about it here as it is still relevant. ‘Merge’ is useful when you have columns from one source that you would like to add to another, the simplest way to think about it is that it works in the same way a JOIN works in SQL, in fact, when you enter the ‘Merge’ wizard there is a ‘Join Kind’ option:

This is how the ‘Merge’ function works:

Append
To solve my issue (explained above), I used the ‘Append’ function. While still combining 2 sources it concatenates one query with another, it’s SQL equivalent would be a UNION ALL. ‘Append’ gives you the option to combine as many tables as you wish, regardless of structure. If a column exists in one query but not another, the column will be filled with NULLS where applicable, this is why it was important to create the two identical views at the very beginning.

This is how the ‘Append’ function works:

When I appended the two sources, I chose the option to create ‘as New’ so I can hide the original 2 queries and I have a nice new table (as seen below) which I can rename, tidy up and make a bit more user friendly, ready for the report consumer to use.

As previously mentioned, data preparation should be done as close to the source as possible, but in situations where this is difficult or simply isn’t possible, it’s important to know about these Power BI functions and how you can use them to your advantage.


More information can be found here:
Append queries – Power Query | Microsoft Docs
Merge queries overview – Power Query | Microsoft Docs

Creating a quadratic solver in Python

In this blog post, I will be showing you how to create a python function that will solve any quadratic equation.

Firstly, what is a quadratic equation?

A quadratic is an algebraic equation that can be arranged as ax2 + bx + c = 0 where a, b, c are known and a ≠ 0. If a = 0 then the equation becomes linear as there isn’t a x2 term.

Secondly, how to solve this equation? There are different ways to solves quadratic equations including:

  • By inspection
  • Completing the square
  • Quadratic formula

We will be using the quadratic formula which is:

Where a, b & c are the known coefficients when the equation is arranged into ax2 + bx + c = 0

We use the Quadratic formula because it can be used in any situation and there is no nuance to it like the other methods.

The first part of the code is to define a function that will accept 3 parameters: a, b & c

To use the quadratic formula, we will use the square root function from the imported math module.

Before plugging the parameters into the formula, we can work out how many real roots we will get by using the discriminant. The discriminant is b2 – 4ac.

If b2 – 4ac > 0 then there are 2 real roots.

If b2 – 4ac = 0 then there is 1 real root.

If b2 – 4ac < 0 then there are no real roots. (There are complex roots using imaginary numbers, but we won’t go into that here, maybe in a part 2)

Let’s add calculating the discriminant and an IF statement depending on the result to the function.

Next, lets plug a, b & c into the quadratic formula.

When the discriminant = 0 we only need to find the value of root1 as it’s equal to root2. Knowing the discriminant = 0, we could simplify the equation to

In the code I have chosen to leave the full formula in.

That’s our code complete, finally let’s test the function with the 3 different discriminant scenarios.

The function works as intended, the code is attached as a txt file.

Making the DAX Engine Work for You

In my last blog post which can be found here, I demonstrated that it’s important to limit your DAX filters to specific columns rather than entire tables, now we know that we can and should do it, let’s explore why and how it works.

There are numerous ways to write an expression that will give the same result set, but why are some ways more efficient than others? To answer that we need to know a little about what happens under the hood, inside the DAX engine(s).

A lot goes into processing a DAX expression but the focus of this blog post will be the Formula Engine (FE) and the Storage Engine (SE).

Both engines do different jobs, think of the FE as the brain, it provides the instructions, and the SE as the muscles, it does the fetching and carrying.

The FE takes a DAX expression and turns it into a set of sequential instructions, (joins, aggregations, filters, etc). It then sends these instructions to the SE. Due to the single-thread nature of the FE to SE communication, the instructions are sent to the SE sequentially. Therefore, the more instructions sent, the longer the entire process takes.

The SE takes these instructions and executes them against the data source(s), collects what is requested and delivers it back to the FE. Unlike the FE, the SE uses multi-threading so can process the FE’s requests very quickly.

So now that we know a little about the FE and SE and what roles they perform, how can we use it to our advantage?

The SE can do a lot of complex work very quickly, but if we can limit the amount of instructions it has to carry out, the quicker the overall process becomes. This is best shown with an example.

*The queries below are being run in DAX studio so the stats can be recorded alongside the result set

Here is a simple expression to select the total amount of sales, per region, taken from everyone’s favourite imaginary online bike store:

DEFINE MEASURE Sales[Count] =
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]
)
)
EVALUATE
CALCULATETABLE(SUMMARIZECOLUMNS(Customer[Country-Region], "Sales by Region", Sales[Count]))

As you can see, the FE takes the DAX expression and turns it into 1 single query. The instruction is sent, the SE does the hard work and passes the result back. The entire query took 44ms to return 7 rows, one per region. Nice and fast.

However, if we add a filter to the query and use the entire Sales table (highlighted in red below) in the filter, look what happens:

DEFINE MEASURE Sales[Count] =
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]),
FILTER(
Sales, Sales[Sales Amount] > 350
&& Sales[OrderDateKey] > 20200101
)
)
EVALUATE
CALCULATETABLE(SUMMARIZECOLUMNS(Customer[Country-Region], "Sales by Region", Sales[Count]))

The expression now takes 8 times longer to complete. The reason for this is the FE sends the SE 8 queries, and remember, it must do these sequentially.

1x The SE fetches the table for the filter (Sales) and trims it down to only the rows that meet the filter criteria:
a. Sales Amount > 250
b. OrderDateKey > 20200101

7x That filtered table, which is now held in memory, is applied across 7 more queries, one query per region in the Customer table.

There are 8 times as many instructions sent to the SE and the whole process takes around 8 times as long to complete.

With a little knowhow we can rewrite the expression in a more efficient way, using only the specific columns in the filter (again, in red). This enables the FE to parse it into a single instruction and ping it off to the SE.

DEFINE MEASURE Sales[Count] =
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]),
FILTER(
ALL(Sales[Sales Amount]), Sales[Sales Amount] > 350),
FILTER(
ALL(Sales[OrderDateKey]), Sales[OrderDateKey] > 20200101)
)
EVALUATE
CALCULATETABLE(SUMMARIZECOLUMNS(Customer[Country-Region], "Sales by Region", Sales[Count]))

The SE receives 1 instruction, produces the same result set and the entire process takes a fraction of the time.

Although the technology behind DAX is very quick, learning the most efficient ways to write expressions can give that technology a helping hand and make your expressions, and therefore your Power BI reports, just that bit quicker.

If you need any help or advice with your DAX expressions or Power BI reports in general, feel free to leave a comment or drop me or any of the team an email with your queries, we would love to hear from you.

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.

Authors:

Alex Whittles
(MVP)
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon