HASONEVALUE vs ISFILTERED in DAX

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:

image

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:

image

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

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:

image

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.

image

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:

image

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

1_

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.

image

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.

2_

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

3_TextFunction

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.

4_

    • 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.

5_

  • Next, select the date picker control.

6_

  • 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!

7_

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 web.powerapps.com 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.

image

Creating a Data Connection in PowerApps

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

image

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

image

Select SharePoint from the list of available connections:

image

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.

image

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

image

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.

image

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.

image

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.

image

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.

ExpenseApp

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:

ExpenseApp_Play

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

image

Thanks for reading.

DAX LASTDATE vs. LASTNONBLANK

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:

image

This simple calculated measures returns the following:

image

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:

image

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.

image

LASTNONBLANK function in DAX

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.

image

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.

image

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

image

Here are the final results:

image

Thanks for reading, enjoy!

Semi additive measures in DAX and Closingbalancemonth

It’s been a while since we have visited Data Analysis Expressions (DAX) on this blog, but now we’re going to jump right in and discuss working with semi-additive measures. Semi-additive measures can’t be added across all dimensions, typically they can’t be added across the date/time dimension. Common examples of semi-additive measures are account balances and inventory levels.

  • Inventory levels can be added up across products, across different stores, but not across time. If you have 500 silver widgets at the end of day on Monday and you have 500 silver widgets at the end of day on Tuesday, how many widgets do you have? You only have 500 of course! We have to take this into consideration when building our model and measures.
  • The same is true of account balances as well. If I have $100 in my account on February 1st and I have $85 in my account on March 1st, what is my account balance? It’s only $85, it’s not the sum of both months.

The Scenario

In this scenario, I am looking at the stock price of Microsoft over time. We want to determine things like Closing and Opening price among others. For this example we are going to try and calculate the closing price for the month using the function CLOSINGBALANCEMONTH. This will present an interesting problem that we will discuss shortly. First, my data model has a simple measure which returns a SUM of the closing stock price as seen below:

image

Remember, this measure is valid for all the dimensions in my data model except for my date dimension. Similar to inventory levels and account balances we don’t want to add the closing stock price across time, this produces incorrect results. Back in 2012 the stock price of MSFT stock was around $30 a share, however, when I display our measure in a table with the year and month what we actually see are numbers that are much higher. This is because the measure is adding up the stock price for all days of the month and this is incorrect!

image

CLOSINGBALANCEMONTH

Definition: Evaluates the expression of the last date of the month in the current context.

Syntax: CLOSINGBALANCEMONTH( <expression>, <dates>)

CLOSINGBALANCEMONTH is one of the built in time intelligence functions and it works great, most of the time. Where it falls short is when you have blanks or gaps in your data. I am going to create a new measure using CLOSINGBALANCEMONTH using the following expression:

image

Now let’s take a look at the results:

image

Most months we are getting the correct value, but some months are blank, why? CLOSINGBALANCEMONTH returns the closing price of the stock for the last day of the month, unfortunately we are looking at stock market data and the stock market is not open every day of the month. So therefore, if the last day of the month has no closing stock price, then blank is returned. See screenshot below. This is typically not what we want when looking at semi-additive measures! We want to return the closing balance for the last day of the month that had a value.

LASTNONBLANK IN DAX to handle blanks!

We need to write a measure in DAX that is going to determine the last date of the month where the stock market was open. We are going to solve this problem by using the function LASTNONBLANK. This is an extremely useful and helpful function.

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

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

I am going to build this out incrementally for demonstration and validation purposes. First, we are going to create a new measure just to see what this function returns:

image

Next, I will add this new measure to our table for validation. Everything looks perfect! The measure is not blindly returning the last day of the month, it’s returning the last day of the month that had a closing price for the stock, meaning the value returned for June, September, and March is exactly what we need.

image

The False Positive

With our knowledge of DAX we may now attempt modify our Close Price measure with the following:

image

This now returns the following results:

image

BOOM! Winner winner chicken dinner, I’m taking the rest of the day off and going to the beach! Wait a minute…. was the topic false positive?

Are the results above correct? Yes the are, just take my word for it or else this blog post is going to really, really long. However, it’s really easy to author formulas in DAX that work at one level but don’t work at other levels, and this is because of Filter context. As developers we have to always consider how the end users might slice the data. For example, if a user is looking at the data at the day level, will our measure still return he closing price for the month? Let’s check.

image

Immediately, we see two different items that tell us the measure is definitely not returning the end of month close price.

  1. First, the close price and the close price (eom) measure have identical values, this means our closing month measure is displaying the closing price for each individual day. That tricky filter context got us again!
  2. Secondly and most obvious, the close price (eom) values should be identical for every day of the month, they are not. Clearly this measure is not working. Back to the drawing board.

Go back and look at the definition for LASTNONBLANK, it works within the current filter context so when we filter our report down the day level it can only return that day.

PARALLELPERIOD IN DAX

Now it’s time to introduce you to one more function in DAX and that is the PARALLELPERIOD function.

Definition: Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.

(I just read that definition and now I’m confused….)

Syntax: PARALLELPERIOD(<dates>, <number_of_intervals>,<interval>)

The PARALLELPERIOD function will return all the dates in the interval that we specify within in the current context. What does that mean??? If you were looking at January 1st and you used PARALLELPERIOD to return all the dates at the month level then a table would be returned with all 31 days for January. This means that we can now return the closing price for the month even if the user is exploring the data at the day level!! I can feel your excitement as I write this.

Let’s jump right in and look at the final DAX expression. I am once again going to modify my existing calculated measure, this time I’m replacing ‘date’[date] with PARALLELPERIOD:

image

Here are the final results:
image

Enjoy!

How to add Data Labels to maps in Power BI

Recently, I was asked if the values for maps could be shown on the map and of course the first thing I thought was to just turn on data labels. Well, if you’re reading this then you already know there isn’t currently a way to add data labels. Now, I say currently because the Power BI team is always making changes and it could one day be there.

image

First, this was not my idea. When I realized there was not a data label option I knew someone in the community would have figured out a work around, and I was right!

Thanks to the awesome people who contribute at community.powerbi.com the answer was only a “google kungfu” search away. If you would like to see the original forum post where Sean “Super Contributor” helped out another Power BI user then check out the following URL:

https://community.powerbi.com/t5/Desktop/Data-Labels-on-Maps/td-p/79118

This blog is going to take a look at the following  three items:

  • Why this workaround works
  • How to set this up and configure it
  • Concerns and issues with this method

Why this workaround works

The map visual in Power BI will allow you to display the value of whatever is allocated to the Location field of the map. Generally you would simply put the country, state, city,  or some other valid location in this field. The limitation is that this field can only be populated with a calculated column. Therefore, Sean suggested combining the value you wanted to display into a calculated column field. Well, that allows you to now display the results, but it doesn’t map the data. The next step is you will need to have the latitude and longitude for each geographical location that needs to be mapped. The latitude and longitude coordinates will be added to the map visual to determine location and the location field will be used to display the data label.

Setup and configuration of Data Labels on Maps in Power BI!

Now, let’s dig in and build this out.

First, make sure your geography table has the required latitude and longitude columns. My geography table goes down to the zip code level of detail but I only brought in the Latitude and Longitude at the State level since I knew this is how I wanted to view the data in my map visual.

image

Next, it’s time to build the calculated column that will help you display the data.

  • Create a new calculated column on the Geography table.
  • The following DAX expression returns Total Sales by State combined with the state name.
  • I also added the FORMAT function to add some formatting to the values.
  • The part highlighted in the red box returns the Total Sales by State.

image

Now that the you have your latitude and longitudes and the calculated column has been built, it’s time to add this to a map! In the below screenshot I highlight where each of your fields should be assigned.

image

Finally, it’s time to display our Location on the map!

  • Under formatting options turn on Category Labels
  • Feel free to change the properties for color, text size, show background and transparency.

image

Concerns and Issues

First of all, you must store your values in a calculated column in order to display them.

  • This means that your values will be static, they won’t change as slicers change.
  • You need to know exactly what and how your end users will use the data, which is nearly impossible.
  • This column will take up resources in your data model.

Secondly, you must have the latitude and longitude for every location you want to put on your map.

  • It may be difficult to obtain a quality list of latitudes and longitudes for each address.
  • These additional columns in your data model will have a high level of uniqueness and will definitely consume valuable memory resources.

Enjoy!

PowerApps–Getting Started

WHOOAAA! PowerApps are awesome! Welcome to this new series I’m starting on PowerApps, this first blog will be a quick one to introduce you to PowerApps and help get you started.

Have you ever wanted to design your own app? Your options with PowerApps are literally endless… maybe you want to build a budget app, a shopping list, a game, or even a checklist. Buildings these apps are not only possible,  it’s also much easier than you can imagine. In no time you will be building your own apps from scratch using Microsoft PowerApps! Here is an example of a really nifty app I built in less than an hour to help score a board game we like to play called Ticket to Ride. Take a quick look at the screenshots below (Animated GIF provided at end of this blog):

SNAGHTML1432121

The “low-code” canvas app

Microsoft PowerApps is a “low-code” canvas based application that requires minimal coding. The coding that you do write is very basic and shares a lot of similarities with excel functions! That’s right, if you have written an expression in excel then you are half way to writing code in PowerApps.

What do you need to get started?

So what do you need to get started? First , you need a license to access Power Apps, fortunately if you have Office 365 you may already have a license! If not, you can sign up for a free trial of an E3 Microsoft Office Account.

  • Sign up for a new trial account here:
  • Enter your required information and then select Create my account.
  • Once you complete setting up your account you will receive a signin, this will be used for logging into Power Apps and Flow.

Next, you need to sign up for PowerApps

image

  • Use the signin from your E3 Office 365 account here.

Finally, you need to create a test environment

  • Navigate to the admin center. Click the settings cog and then select Admin Center:

SNAGHTML177acba

  • Next, click new environment
  • Give your environment a name, select a region, and environment type
  • Select the Join preview program box.
  • Create environment
  • Create database – > Choose currency and language options.
  • Select Include sample apps and data and then create the database by clicking Create Database.

Congratulations! You’re ready to start creating your own apps!

Thanks for checking out my blog, in future blog posts in this series I will show you how to quickly build apps so check back often!

Here is the animated GIF of the Ticket to Ride scoring app I created using PowerApps:

Ticket to Ride