Dynamically changing title names in Power BI

December 29, 2020 / Mitchell Pearson / Leave a comment

Hey Everyone! I’m excited that I’m finally getting this blog done! Also, I’ve embedded the YouTube video below Smile

Also, in other news, after 8 years of blogging I finally decided to make some kind of logo, it’s a little unique but I like it! Especially how I was able to sneak the board game in there, because I love board games!

Figure 1 – New logo

Here is the video posted on YouTube if you want to watch the steps in action.

What is a Dynamic Title in Power BI?

The idea around a dynamic title is that the name of the title changes automatically to reflect any filters that may be applied. In the automated gif below, watch how the title name changes as I drill down into the chart visual.

Figure 2 – Dynamic title, animated gif

Creating Dynamic Titles in Power BI with SELECTEDVALUE() Function

Creating the dynamic title you saw in Figure 2 above requires a little DAX, not much. First, create a new calculated measure and give it a name, in the YouTube video I called this measure Dynamic Title. Then I used the following expression:

This is a simple expression which will return the name of the country if only one country exist in the filter context, if not, it will return the text “All Countries”. In this example I am navigating a hierarchy, therefore, I want the title name to change for each level of the hierarchy as new filters are being applied. The following screenshot is the completed code from the video:

Conditional Formatting in Power BI on the Title of a visual

The final step is to use the calculated measure on the visualization with the following steps, see animated gif for reference:

Select visual and navigate to the formatting pane
Find the title category and expand it.
Click on the fx icon next to title
Select field value for Format By
Select your measure from the drop down list for Based on Field.
That’s it!

As always, hope this post helped!

Quick Tips–Updating Parameters from the PBI Service

July 23, 2018July 23, 2018 / Mitchell Pearson / Leave a comment

In this quick tip I want to share how you can update Parameters in Power BI from the service. Previously, this was not an option and Parameters could only be updated from Power BI Desktop.

Updating Parameters in Power BI

To update parameters, navigate to the Datasets section in the Power BI Service. Next, click on the schedule refresh icon. Updating parameters is done from the schedule refresh window.

Click on Parameters from the schedule refresh window. Once expanded you will see available parameters, simply update the parameters and you’re done!

Thanks for reading!

Quick Tips – Connecting to a SharePoint Folder in Power BI

June 4, 2018 / Mitchell Pearson / Leave a comment

Recently, when teaching the Advanced Power BI Course by Pragmatic Works, I had a student ask how to use the SharePoint Folder in Power BI. She had been struggling with getting it to work correctly. After trying a few different URLS I could see why she was having a problem. Since you’re reading this, you are probably having the same kind of issues. The error message is actually pretty good, but a little confusing. When the error says “the site’s root URL only.” I took this to mean SharePoint’s URL, however, it actually means the URL of the site within SharePoint.

The URL isn’t valid. Please enter the site’s root URL only.

Let’s take a look at how you can properly connect to a SharePoint Folder in Power BI and extract the files.

Connecting to a SharePoint Folder in Power BI

In my first attempt I tried connecting to simply the root of my SharePoint site.

https://pragmaticworksonline.sharepoint.com

This only allowed access to the Shared Documents folder on the root and therefore didn’t work.

As you can see in the above image, this only allowed access to the Shared Documents folder on the root of the SharePoint site.

Connecting to a specific SharePoint site

Next, I tried connecting directly to the root of a particular site. I connected to the training site on SharePoint using the following URL:

This worked, the SharePoint folder option will traverse subfolders, just like the default from folder option. Therefore, if you are looking for a particular directory then you can simply apply a filter in the Power Query Editor on the Folder Path property.

Thanks for reading this blog!

Quick Tips – Mapping Geography Data in Power BI

May 16, 2018May 16, 2018 / Mitchell Pearson / Leave a comment

One challenge of working with data of a geographical nature is that sometimes, it can be mapped incorrectly. In this quick post I want to give you a couple of tips that will help you to reduce, if not eliminate, incorrect mappings of your data!

There are a few different methods you can use to try to solve the issue of incorrect mapping of geographical data.

Use hierarchies in your map visuals, hierarchies store relationships between attributes and can help with mapping a lot. A geography hierarchy might look something like the following: Country –> State –> City – > Zip
Use data categorization. Sometimes a state can share the name with a city or a country. I remember years ago when I heard on the news that Georgia was under attack, that was pretty concerning for me since Florida is very close to Georgia ha ha. Of course, the state of Georgia was not under attack, it was the country! We can use data categorization in Power BI to specify that a column is a city, state, zip, or country.
Remove ambiguity, for example, instead of having a city column, create a new column with the city and state. Then you can assign that new column a data categorization of Place. If you have millions of potential combinations then this may not be feasible within Power BI, this column would have terrible compression and most likely exceed any memory limits. However, this method works great.

Using hierarchies in Power BI to map geography data types

Take a look at the screenshot below, the State/Province of Nord in France is being incorrectly mapped to Lebanon.

Fortunately, this one can be solved very easily by using hierarchies. Nord, by itself, is not clear enough for Bing maps, however, if we add the country to the visual as well, then the picture becomes clearer and Nord will be properly mapped to France.

1) Add the Country to the Location. The country should show up above the state in the location list as seen in the following screenshot:

After adding the country, the map is at the highest level and you would want to now drill down to show the next level in the hierarchy. In the animated gif below, you will notice that Nord is now being mapped correctly in France!

Using the PLACE data categorization in Power BI to map geographical data

In the following image, you will notice the map visual has been filtered down to the state California in the United States and therefore only the cities that exist in California should be displayed. Yet, the map visual is a little confused, and this happens because multiple states could have the same city name.

To solve this confusion you want to remove the ambiguity here and create a new calculated column with the city and state combined. Next, assign the new column a data category of Place. See demo below:

Replace the column city in your map visual with the new column city, state. Here is the final result, the cities are now mapped correctly and only cities directly related to California appear on the map:

Thanks for checking out this “Quick Tips” blog. Please check out my YouTube channel to find more Power BI related material!

Quick Tips – Quickly Changing Connections in Power BI

May 7, 2018May 16, 2018 / Mitchell Pearson / Leave a comment

Hopefully, you don’t have to go in and change your connection strings often in Power BI, but when you do, you will want to know this little trick.

What’s not covered: Parameters

In this blog I’m not going to discuss parameters, although as a developer, I love that parameters are available for administration. Even better, the use of parameters in Power BI became even more powerful with the recent update to Power BI where parameters can now be changed in the Power BI Service! I will definitely do a blog or series on parameters in the near future.

What is covered: Power Query Editor & Data Source Settings

As a trainer, I come across the issue of needing to change connection string information quite often and this saves me a lot of time. Let’s jump right in. In this example, I have a power bi report with two separate data sources and a total of eight tables.

The first data source is to an excel file with many different sheets.
The second data source is a separate excel file.

Imagine that both excel files get moved from one directory location to another directory location. Typically this would mean that I would need to individually update the connection information for all eight tables in my data model. However, I’m here to show you a better way.

Launch the Power Query Editor

You will be making source connection changes from the Power Query Editor. Launch the Power Query Editor by clicking on “Edit Queries” found on the Home ribbon.

This will launch the Power Query Editor. Next, click “Data source settings” found on the Home ribbon.

This launches the Data source settings window and you will be able to see all of your connections here. If you had twelve tables all coming from the same excel file or a SQL Server database, ect… then you would only need to change one connection for all twelve tables. You get the idea!

Select the first connection in your list you want to update and then click on Change Source…

This will launch a new window that allows you to quickly make source changes, this window will look slightly different depending on your data source. This example is loading data from an excel file, therefore the following window is provided for making changes:

After clicking browse, you can now browse to the new file location. In this example, the file has moved over to the DAX Advanced folder so I want to point to that directory and file, as you see below:

Once the file has been selected, click Open. Click OK. Now all seven tables that were pointing to that AdventureWorksDW excel file have been updated at one time. Repeat this process for any remaining data sources that may need updating and you are done!

I hope you enjoyed this Quick Tip!

SQL Tips! How to edit the query for Edit TOP 200 Rows in Management Studio (SSMS)

February 18, 2015February 18, 2015 / Mitchell Pearson / 3 Comments

It has been some time since I have written one of my SQL Tip blogs. I like to use the “Edit Top 200” rows functionality provided by SSMS when making quick but different updates across like records in SQL Server. Did you know you can edit the top 200 rows query and get the records you’re interested in updating? We can do this in just a few easy steps.

First open up the “Edit Top 200 Rows” window in SSMS for the table you want to edit.

Second, Right click at the top left of the table and from the drop down list select Pane > SQL.

Next, now we see a SQL statement that we can edit. Here I simply added the where clause WHERE MakeFlag = ‘TRUE’

Once the SQL has been updated it needs to be executed. Right click on the top left corner of the table again. This time select “Execute SQL”.

Below you can see the new result set available now for editing!

As always thanks for looking.

How to use the Optimize For hint to force the execution plan you want.

January 27, 2015January 27, 2015 / Mitchell Pearson / Leave a comment

Quite some time back I found myself fighting with an Execution Plan generated by SQL Server for one of my stored procedures. The execution plan always returned an estimated row of “1” when processing for the current day. I won’t go into details on why this one specific stored procedure didn’t use the older cached plans as expected. I will however tell you like most things with SQL Server there is more than one way to solve a problem Smile .

This method is something I have personally wanted to blog because it’s something I have only used a handful of times when I just couldn’t get the execution plan to work the way I wanted it to. Note that using this hint we are forcing the SQL Server Optimizer to use the statistics for the specific variable value that we provide. However if the table was to grow significantly in the future we may be hurting performance by forcing a bad execution plan and that is a drawback to using this hint, so now you know!

Take a look at the two screenshots below. The first is the estimated rows from the Fact Internet Sales table and the second is the estimated execution plan.

What I actually want to see for this execution plan is HASH MATCH. This will perform significantly better for the number of records that I will have. Unfortunately due to out of date statistics I’m getting a bad plan.

So let’s note two things.

First, in most situations the best solution here is to simply update statistics. This should be part of ANY database maintenance plan.

Second, The example I am using here is not great. I am simply forcing the plans to do what I want for demo purposes.

Let’s take a look at the original query:

DECLARE @ShipDate DATE = '1/1/2008'

SELECT 
       [EnglishProductName] AS Product
      ,[SalesOrderNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM 
    [dbo].[FactInternetSales_Backup] FIS
  JOIN
    [dbo].[DimProduct] DP
  ON
    DP.ProductKey = FIS.ProductKey
  WHERE ShipDate > @ShipDate

Now we are going to modify this query quickly to use the Optimize For hint. This hint is going to allow us to optimize our Execution Plan in SQL Server using the specified parameter. In my instance this is going to be a previous date where I know the statistics are reflective of what I want to see in my execution plan.

Here is the modified query:

DECLARE @ShipDate DATE = '1/1/2008'

SELECT 
       [EnglishProductName] AS Product
      ,[SalesOrderNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM 
    [dbo].[FactInternetSales_Backup] FIS
  JOIN
    [dbo].[DimProduct] DP
  ON
    DP.ProductKey = FIS.ProductKey
  WHERE ShipDate > @ShipDate

  OPTION (OPTIMIZE FOR (@ShipDate = '1/1/2005'))
GO

In this query the result set returned will still be for the original value of the variable “1/1/2008’. However the SQL Server optimizer is going to generate the plan using the OPTIMIZE FOR hint that we provided. (Highlighted in Yellow).

Now let’s take a look at our new Estimated Execution plan:

This time we are getting a Hash Match which is much more applicable for our table and the number of records that will be queried.

As always, Thanks Smile

SQL Tips! Generate DDL Scripts with the data from the table. (Installment 2)

September 14, 2014 / Mitchell Pearson / Leave a comment

Welcome back to this series on SQL Tips. Sometimes I find myself needing to not only generate the DDL for a table but also the data that is stored in that table. For example imagine I have created and populated a few tables for a blog post. It would be much easier if I could share the table definition and the SQL statement to populate that table, Agree? Great!

Scenario

In this scenario I am going to show you how to generate the DDL to create the DimCurrency table from the AdventureWorksDW2012 database. Along with the DDL script I will also show you how you can generate the script to populate the table.

Walkthrough

Open SSMS and connect to the server where your table is located at.
Right click on the database where your table is stored, I’m using AdventureWorksDW2012.
Next click on Tasks
Next click Generate Scripts
Inside the Generate Scripts wizard click next on the introduction screen.
For Choose Objects screen –> Click “Select specific database objects”.
Expand the tables list.
Select the DimCurrency Table.
On the “Scripting Options screen” I’m just going to load the script into a new query window. Please select “Save to new query window”. This option is found at the bottom of the screen.
On the same screen select the “Advanced” button found on the right side.
Now scroll down to “Types of data to script” and change it from schema only to Schema and Data.
Click Ok. Click Next, Click Next. The script will now be created and generated in a new query window. Click Finish once it has completed. Below is a partial screenshot of the completed product.

As always, thanks for looking!

SQL Tips! Set your default database in SSMS (Installment 1)

September 11, 2014 / Mitchell Pearson / Leave a comment

In my time as a Business Intelligence consultant and trainer I have picked up some pretty cool tricks and thought I would start blogging on these tips two or three times a week as I catch myself using them.

In this blog I am going to show you how you can set your default database when you connect to a server. I find this to be an extremely helpful little tip. Please see below.

Walkthrough:

Open SSMS and from the object explorer click “Connect”.
From the Connect to Server prompt: Enter your server and Authentication information.
Click “Options”, located at the bottom right.
Next click the “Connection Properties” tab found at the top.
Locate “Connect to Database” and then click the drop down menu.
From the dropdown menu select <Browse> and then select the database that you want to be your default. Click Connect..
Voila! Now every time you connect to that server it will default to that database instead of master!

As always, thanks for looking!

SSIS Performance Tuning Webinar–Q & A

August 22, 2014August 22, 2014 / Mitchell Pearson / 2 Comments

Thank you everyone for attending my webinar on SSIS Performance Tuning, if you missed that presentation you can watch it here: http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=683

Below are some of the questions that I received from the Webinar:

Q: Can you give some performance tips in using script tasks too?

A: Yea, don’t use them! Script tasks can be really bad for performance and many times I have been able to replace someone else’s code with native SSIS components. For example, I had situation where the client was using some very complicated .net code to parse out columns based on logic. The package was taking over an hour to process 1 million records. I replaced this with some conditional splits and derived columns and it now runs in 3 minutes.

Q: I am assuming that the file formats must be the same for all files when using the MultiFlatFile transform, correct?

A: You are absolutely correct. The metadata in each file must match.

Q: PW delivers a ‘TF Advanced Lookup Cache Transform” component. What are the benefits of using this component over the Cache Transform covered earlier? It seems that the TF components cannot make use of the same result set when the data set is role based.

A: For basic caching of data I would use the native SSIS cache transform. The major advantage you get from the Task Factory component is you can do very difficult range lookups with ease and they will perform at a high level. Please see my blog post on this.

Q: What version of SQL Server is being used?

A: I was using SQL 2012, but everything in the presentation is applicable to 2005 and 2008.

Q: With the multi flatfile connection manager can you specify specific types?

A: Yes, the wild card character can be anywhere in the connection string property. So you could do test*.txt to only pull in text files where the file name begins with test.

Q: Why would you ever not use table or view (fast load) option in the OLEDB Destination?

A: Well I personally would always use that option. However, with the fast load option all records are committed for the entire batch. So if there is a record that is bad and causes the failure you will not know which record caused the error. With table or view option each record is committed individually so you know exactly which record caused the failure.

Q: is the volume on?

A: Sorry can you ask that again, I couldn’t hear you….

Thanks again!

	VINICIUS AUGUSTUS PA… on Azure Data Factory–Filte…
	Mitchell Pearson on Advanced TSQL Takeover
	lordi on Advanced TSQL Takeover
	Mitchell Pearson on The transaction log for databa…
	You cant see me on The transaction log for databa…

Mitchellsql

Tips and Tricks