Using a Journal

In this blog, I’m going to introduce you to my personal journaling method. I have, over time, developed a journaling technique that works great for me. I began my journal experience by scribbling aimlessly in journals with no form of organization. Eventually I was introduced to a journal technique called bullet journaling. I started initially with this technique and over time made many adjustments that have grown to fit me very well.

This blog post is broken down into many sections with smaller sections.

  • What’s in my journal
  • Why do I have a journal
  • How do I set up my journal

Part 1 – What’s in my Journal

So what’s in my journal? Glad you asked Smile, at a high level my journal contains checklists, goals, tasks, ideas, stories, quotes, notes and much more from virtually every aspect of my life. Yes, you read that right. Somehow, I have created a journal that allows me to store information about my work, family, faith, personal development and everything in between into a single journal.

My journal is a checklist, it keeps me on track and focused on specific goals. It’s also a diary. I keep track of successes and failures. I track stories of friends and families. I also include contacts that I meet, mistakes that I make, processes that I feel can be improved.

In the back of my journal I have an area called Brain Dump, this is where I dump ideas and thoughts and to do list items that pop in my head. I also have an index, organized categorically, that helps me quickly find items stored in my journal, this is the latest addition to my journal method and one I’m very excited about.

Part 2 – So why do I journal?

I journal because I want to remember, I want learn, I want to grow and improve and become better in virtually every aspect of my life.

I want to remember stories of my children growing up, good times that I have with family and friends. I want to remember people I meet, I want to remember details that they shared with me, I want to remember the sad times and the tough times so I can appreciate the good times and use persevere through difficult times.

I want to learn, grow, improve, and become better in virtually every aspect of my life. The journal gives me an opportunity to record a written record of my life. If I have successes I record those so I can try to duplicate those efforts, likewise if I experience failure or make mistakes then I record those so I can use my past as a learning experience to grow from.

Jim Rohn encourages journaling for many reasons, he especially encourages one to record their mistakes:

“Mistakes in judgement are nothing to be ashamed of, surely most of our personal growth comes as a result of our errors. But what is truly unforgivable is to make the same mistake twice, every mistake has its’ own price tag. But the most costly error anyone can make is an error unlearned and often repeated.” – Jim Rohn

The process of reflection and refinement

I have found the most valuable part of keeping a journal is not recording a history of events, no, I have found that the most valuable part of keeping a journal is going back over a day, a week, a month and reflecting on those events. I reflect on mistakes and remember what those mistakes felt like and try to make sure that I make the necessary changes to not repeat those mistakes. I reflect on my successes and look for ways to improve those processes, I reflect on my failures and, likewise, improve the process to verify those failures don’t occur again.

This is an eye-opening process and one that has helped me to evaluate and redefine even the most mundane of activities on a weekly basis.

  • I no longer take coffee or water on the plane. Having these uncovered drinks disrupts the process of doing work and so I opt for always carrying water on the plane with me.
  • I no longer turn down the air when I enter a hotel room. I have discovered that I personally am much less motivated to get work done when the hotel room is cold!
  • I have removed all games from my phone.
  • I schedule high-priority tasks during high-energy parts of my day and I schedule meetings at lower, less efficient parts of my day.
  • I now regularly sit in the back of the car when the family is commuting across town, this allows significant time with the kids.

This is a tiny list of modifications I have made in my day to day life and are literally items that came to mind as I was writing this blog. I have a lot of other things that are being recalled to memory at this very moment, the process of reflection can not be underestimated!

Part 3 – The Setup!

I’m very excited about the way I set up and configure my journal, the best way to see this is by watching, take a look at the following video:

2 things you need to know about Power BI Security

In this blog post, I want to quickly discuss 2 things you need to know about Power BI Security as it relates to the Power BI Service. Many of the clients that I visit and perform training for are just getting into an implementation of Power BI and are not aware of these two items. I will probably come back and update this list as there are a couple more items I want to add, but those will take slightly more explanation! Smile

Publish to Web should be disabled

The publish to web feature provides an Iframe that allows a user to take a report and then embed that anywhere an Iframe can be accepted. The problem with this is that now the report is 100% security free and anyone who has access to the report has access to everything in it.

If you choose not to disable this feature it should, at the very least, be limited to specific people within the organization who have received proper training. To disable or manage this feature you must perform the following steps:

  • You must be a Power BI Administrator
  • Next, select Admin Portal from the settings wheel found in the Power BI Service.


  • From the admin portal, select Tenant Settings and then expand Publish to Web.
  • Click on the button to disable Publish to Web feature.


Direct Query to a relational database is not a RLS enabled data source

Many users, especially users on later versions of SQL Server, assume incorrectly that a direct query connection to SQL Server will use the security restraints applied at the database level. Unfortunately, this just isn’t the case.

Any user connecting to a report with Direct Query is using the stored credentials in the enterprise data gateway and therefore will have access to whatever the data gateway has access to. So if you have enabled row level security on the tables for your database, this is not going to work with direct query.

However, you can and should add row level security to your power bi data model (pbix) file. This row level security will work and limit access as it is intended!

Not sure how to set up Row Level Security in Power BI? Thanks ok! Click Here

Download and unzip files using R in Power BI

In a previous blog, I wrote about how you could use R to unzip files. Well, that raises the question, can I download a zip file and then unzip it using R?

You can view the previous blog post on how to unzip files here.

Update: YouTube video can be found at the bottom of this post!

Download files using R

In general, downloading and processing a file can be done in Power BI very easily using “web” as the data source and you never have to worry about using R. However, if you need to do something more complex then R can be very helpful. For example, you may want to download a zip file, unzip it, and then process it.

In this example, I am going to download a zip file that contains names by state, this zip file is stored in the following location:

This zip file has data for each of the different states, for this example I only want to look at the data for Florida. So we are going to unzip the file and only extract the data from the single file that we need data from.

Let’s take a look at the code in R Studio and break it down:


  1. First, I want to store the destination location and filename in a variable, this is done in lines 1-2.
  2. Next, I want to download the zip file and store it locally, this is done in lines 4-6.
  3. Thirdly, now the file will be unzipped and the file FL.TXT will be stored in the variable FLnames, lines 8-9.
  4. Lastly, we will read the text file and store it in the variable babynames.

The only thing we added in this example that was not in the previous blog post was the ability of downloading the code. If you, missed the previous blog and want to know how to import the data into Power BI, you can check that our here.


Thanks, for checking out my blog!

Using R to unzip and process files into Power BI

There are a lot of blogs and videos out there on how to use R to enhance the visualization layer of Power BI. In this small series, I want to focus on how R can be used to improve the data importing and data cleansing capabilities of Power BI. Previously, I wrote how you could use the R language in Power BI to export data out to a CSV file. You can view that blog by following this link.

Update: Video embedded at bottom of blog post.

Although, I personally am not an expert when it comes to R, I’m learning more each day, it’s one of the topics I enjoy teaching the most in our Advanced Power BI Boot Camp.

Now it’s time for the required Pirate Picture: ARRRRRRRR. By the way, this image is from a tabletop game I like playing called Merchants & Marauders!!

Image result for pirate arrrrr

Getting Started with R in Power BI

To work with R you will need to have R installed. I use Microsoft R open, link provided below. You can optionally download R studio as well. Both of these are free downloads.

Required: Download the Microsoft R Open

Optional: Download R Studio

Discloser: At the time of this blog post, there is no built-in intellisense in Power BI to help write and debug R. Therefore, I use R Studio to write and test my code! R Studio is not a requirement, it is, in my experience, just very helpful.


The problem I want to solve in this blog post is how to unzip a file with Power BI and then process that file. Because I come from an enterprise BI background my first thought is to approach this problem with a tool like SSIS or PowerShell. However, there is an easier way, why else would we be here! Smile

I want to show you how you can use R to unzip and then extract data from a file all in one step. By the way, it’s really, really easy!

Let’s start off by looking at the code, this is all of it!:


  1. On line one, we are declaring a variable called data, this variable will store the results of our expression.
  2. On line two we are using read.csv to extract the results from the csv file, Customer Sales, and these results are stored in the variable from step one, please note that this process can not occur until after the file has been unzipped.
  3. On lines three and four we are using UNZ to unzip the file Customer and then return the file Customer Sales.csv.

And that’s it!

Wait, wait, wait…. What is line seven? Well, line seven allows me to test the results in R Studio. This is not needed for the importing data into Power BI Desktop. Oh, that reminds me, we need to import the data into Power BI Desktop!

Importing data using R in Power BI Desktop

As you are already aware, there are a lot of data sources available in Power BI, it shouldn’t surprise you that one of those data sources is R. Now let’s walk through how we can quickly import data from an R script into Power BI Desktop.

From the home ribbon in Power BI, click Get Data –> Other –> R Script. See screenshot below:


This will launch the R Script editor. As I previously mentioned, this script editor is simply a text box. Now we simply paste in our code from R studio, or if you happen to be an R aficionado you can just type your code in this box, as seen below:


Once we click ok, the Navigator window will open. Since we only declared one variable in our script, only one option will be provided. From here, everything is exactly the same as any other data source.

4_Navigator Window

Ok, now we’re done! I hope you enjoyed this blog post and I look forward to writing more, until next time.

Using Composite model with Dynamic Security in Power BI

Like many others, I have not been an advocate of using Direct Query, it has long had many concerns diminishing its’ overall usefulness with one of the major key issues being performance. The Microsoft Power BI team has made major strides to mitigate the concerns around performance by adding two new key features: Composite Model and Aggregations. This blog will not discuss aggregations. However, we will briefly discuss the composite model and how we can use this to make Dynamic Security better.

The composite model simply allows users to import data and use Direct Query in the same data model. This has long been a point of contention for Power BI users because combining these two features into one model adds a lot of value. In this blog, I am going to briefly explain how this model helps to improve dynamic row level security.

I was sure I had previously blogged about dynamic row level security but I don’t see a post so I am going to re-direct you elsewhere. If you are interested in learning exactly how to implement dynamic security you can check it out here: Dynamic Security implementation

I am a huge advocate for Dynamic Security. Dynamic security allows you to create a single role in Power BI and then you can manage all your user security in a single table. Take a look at the following table which I am now storing in SQL Server:


So why would the composite model make dynamic security better?

Great question. Historically, I would always import data into Power BI, including the dynamic security table. The problem with this method is what happens with the security of the data if the model is not being refreshed? For example, I may reassign which users have access to which sales territories in my security table but if data models in the service are not being refreshed then the security is not being refreshed and thus users will have access to data they should not have.

Now, with the composite model I can still import all my reporting data but then I can set up a direct query connection to my dynamic security table. This means that all of Power BI models using this table will always be up to date!


Hopefully you can find many more useful scenarios for the composite model! Thanks for reading my blog!

Power BI Resources

This blog post is a quick post to outline some of the resources around Power BI that I have found most useful, I will update this list accordingly.

Power BI Learning Tour

Brand new to Power BI? If so, this Microsoft led training is a great place to start:

Custom Visuals – Series by Devin Knight

Devin Knight has, at the time of this blog, recorded 117 videos on his blog dedicated to demonstrating different custom visuals created by the community. If you are not already aware of this blog series, take a look at the link below!: – Custom Theme Generator

My personal favorite from is the custom theme generator. They also have pre-built layouts, tutorials and many other valuable resources.

DAX Guide

The DAX Guide is a great place to browse and explore available DAX functions.

Have something that just has to be on this list? Let me know!


I use the function HASONEVALUE quite often, many times it is in the context of solving problems with the total row. I wrote a blog on how to handle totals using HASONEVALUE here:

Unfortunately, HASONEVALUE is not always the tool for the job. For this scenario I am returning Homes Sold Month-to-Date. The final solution should look like the following screenshot:


As you can see in the previous image we want to return the Homes Sold MTD if we are at the day level or the month level, but if we are at a level higher than the month level then we simply want to return a blank value. It doesn’t make sense in this scenario to show MTD homes sold at the quarter or year level. If we use HASONEVALUE(‘date’[Date]) here, then no value is returned at the Month level. Here is an example:


As you can see in this example, a blank value is returned for the month of January. As previously mentioned the desired behavior is to return a value for the month of January.


IsFiltered: Returns TRUE when columnName is being filtered directly. Else, False.

Let’s take a look at what IsFiltered returns if we are checking to see if the date is filtered:


ISFILTERED returns False for the month and Year as expected. Now, we can simply chain multiple IsFiltered functions together to return our desired results. In the following screenshot I added an or condition to check if the month was also filtered. As you can see, the results now return TRUE at the day and month level.


Now we can apply some conditional logic using the IF function based on the results of the IsFiltered expression in example above. If the day or month IsFiltered then return the MTD homes sold, else return BLANK(). See below:


Thanks for checking out my blog!

PowerApp customizations

In my last blog post, you learned how easy it is to quickly create your first app. In this follow-up blog I want to show you how you can make some basic customizations to the app that was created in the previous post.

You can find my previous blog here:

What’s covered

In this post we are going to quickly cover the following customizations:

  • Renaming
  • Currency Formatting
  • Default properties


In this first image we can see that the name of the title on the screen has an underscore instead of a space. Secondly, we see that the default value of the expense amount is not consistent. We are going to quickly update each of these items.

Renaming controls:

  • First, select the title. This can be done by either clicking the title directly or by finding the text input control from the left navigation bar.
  • Secondly, select text from the drop down list of properties on the control selected, this may be the default property.
  • Finally, update the text value in the formula bar. Replace the underscore with a space.


Customizing currency formatting

Now we are going to update the currency formatting. To update formatting we are going to use the Text function and we will once again be working in the formula bar.

  • Select the control currently displaying the expense amount. Once again, you can select this control directly from the app or select the control from the Left Navigation bar.
  • Make sure Text is selected from the drop down properties box, this should be the default selection.
  • Update the formula bar to format the output text, see code below image.


The formula is Text(ThisItem.Total, “$#,###.00”), see image below:


Setting Default Values

The final customization we will make is to set default values, this will make working and interacting with the app a much better experience. For example, the date will always default to the current date when adding new expenses.

I’m going navigate to appropriate screen by selecting the EditScreen1 from the Left Navigation bar, this will take me to the edit screen where we can edit existing records or create new records. This behavior of being able to edit or create new records is done through the Edit Form control. The form control is slightly unique because the controls inside the edit form are stored in cards and those cards are Locked! Let’s unlock the card so we can customize it:

  • First, select the card control from the Left Navigation bar.


    • Next, navigate to the properties window found on the right side of the screen and select Advanced, in the advanced properties click Unlock to change properties. This will now unlock the card so that we can edit the controls.


  • Next, select the date picker control.


  • Finally, update the formula for DefaultDate to simply Now(). As you can see in the screenshot below, the date of the date picker control will now default to the current date whenever a new record is being added in the app!


As always, thanks for reading my blog.

Creating your first PowerApp in 10 minutes!

Microsoft PowerApps is a canvas based application for creating line of business applications. In other words, you can create an app for your organization by using a drag an drop interface! Personally, I love the capabilities and ease of use of PowerApps. What makes PowerApps even cooler is the fact that you can build apps really really quickly!

In this first blog post of many on working with PowerApps I want to focus on creating an app that has little to no customizations. This post will focus on a step by step approach to creating your first app. We are going to use an option in PowerApps that will build an app for us, using our data. Yes! Build the app for us! We could customize this and make it our own and there is a lot we can do but I want to get you started so we want to keep this first post simple.

To build our app we need a data source. For this example I am going to use a SharePoint list to keep it simple. If you want to use data stored on-prem this requires slightly more work, not much, but slightly more and so I will come back to this in a future blog post.

Let’s discuss the steps we will take to build our first app:

  1. Create the data source and populate it with sample data.
  2. Log into and create a connection to our data.
  3. Create a new app using the option: Create from data.
  4. SAVE APP!

It’s really that simple! Now let’s take a look at the data source.

Using SharePoint as a data source for PowerApps

In the below image you can see I have created a SharePoint list called Expense_Blog and I have added five columns. I then populated the list with a single row of data so I would have some sample data to preview my app with. I prefer to always start with sample data as I have found this makes building and customizing the app easier.


Creating a Data Connection in PowerApps

Open up your browser and connect to From the left navigation pane, expand data and the click connections.


This will open up a window with all the connections you have already created. To create a connection to SharePoint, click + New Connection.


Select SharePoint from the list of available connections:


Once you select SharePoint you will be prompted to enter your credentials, and this will create the connection to SharePoint. Next, navigate to the home tab, then hover over Start from data and click Make this app.


Clicking Make this app will launch Once launched you will be asked to connect to your data. Select SharePoint.


Now you need to connect to the specific SharePoint site where your list has been stored. My list has been stored in my PowerApps99 site.


Select your list from the options available and then click CONNECT. After you click connect your app will be created. You will get a pop up screen, you can just click SKIP for now.


The created app comes with three screens.  A display screen, a details screen, and an edit screen. As you can see below the display screen will show all of our current expenses, unfortunately it’s not showing the columns we want to see. I’m going to show you how to use the GUI to customize the display screen to show the correct columns.


To customize the display gallery, take a look at the below animated gif. Essentially I select the gallery, then go into the properties and update the columns.


Now let’s test our our new app by previewing it. Click on the play icon at the top right to preview the app. Take a look at the animated gif below:


The last step is to save the app. Click File –> Save as –> The Cloud –> Blog Expense App –> Save!


Thanks for reading.


In my previous blog post I discussed how the time intelligence function CLOSINGBALANCEMONTH worked great, except when it didn’t. If you remember, the problem was the function CLOSINGBALANCEMONTH could not handle situations where the data had gaps, or blanks on specific dates.

If you missed the previous blog post, you can find it here:

In this post,  I want to build a measure that returns the closing price for the date in the current context. My initial attempt at this measure is going to suffer from the same issue we faced when working with the CLOSINGBALANCEMONTH function in the last blog. Let’s start by using CALCULATE and LASTDATE.

Working with LASTDATE

Here is your MSDN definition of LASTDATE: “returns the last date in the current context for the specified column of dates”.

This function is great because it works in the current context so it makes the measures you author in DAX very dynamic. For example:

  • If the current context is month, LASTDATE returns the last day of the month.
  • If the current context is Quarter, LASTDATE returns the last day of the quarter.
  • If the current context is day, the day in the current context is returned.

This means that the LASTDATE function automatically works for each level in your date hierarchy, this is why we like working with DAX, because of this type of functionality.

Let’s take a look at a simple example of LASTDATE:


This simple calculated measures returns the following:


As you can see in this screenshot, LASTDATE is returning the last date of the current context, in this visual we have the month and the year.

Next, I will create a new measure that returns the Closing Price of the current time period. The following measure returns the Closing Price of the stock for the last day of the month:


If we take a look at the results in our table visual, we will see that our measure is returning blanks for certain months. The reason this is occurring is because the stock market isn’t open every day of the year, therefore, if there is no closing price for the last day of the month then a blank value is returned.



I discussed the LASTNONBLANK function in my previous blog post, so I won’t get too detailed here but here is the definition and syntax from MSDN:

Definition: Returns the last value in the column, filtered by the current context, where the expression is not blank.

Syntax: LASTNONBLANK(<column>, <expression>)

LASTNONBLANK will return the last date in the current context that wasn’t blank, that is the perfect function for this scenario.


Let’s take a look at the results of LASTNONBLANK compared to LASTDATE. In the highlighted sections below, notice that for each area where the close price is blank the results of the LASTDATE function and LASTNONBLANK function differ. As previously discussed, the stock market was closed on the last date of the month and therefore the close price does not return a value.


Now it’s time to modify the Close Price measure so that it returns the last close price for the current context:


Here are the final results:


Thanks for reading, enjoy!