Unexpected Totals in DAX (Part 1)

DAX is an awesome language and when paired with properly created relationships, DAX can add significant analytical value to your data models with minimum effort. But, you already knew that! So why are we here? Well, sometimes, DAX can produce results that are unexpected and this is usually very noticeable when looking at the totals row.

So why do we get unexpected results at the total row? Why doesn’t the total row simply return the sum of all the rows in a specified column? The simple answer is your DAX calculation is also computed for the total row and operates within the contexts of the total row. Let’s take a look at the previous screenshot.

In this example, the total row is returning all homes listed for all years in the filter context, this is in fact the sum of all the rows in the column. However, you may expect to only see the homes newly listed on the market for the latest time period, in this case that would be how many homes were listed in 2016. The result of 8,691 homes listed in the total row is not wrong or incorrect, it depends on what you are specifically looking for, it could definitely be unexpected depending on your analytical needs. If you do not wish to see the total of all homes ever listed in the current filter context then you have a couple of options available to you.

There are generally 3 ways you could address incorrect / unexpected totals.

1. Pretend the problem doesn’t exist. (ProTip: Don’t ignore problems.)
2. Identify when the DAX calculation is being evaluated for the total row and return a BLANK value.
3. Identify when the DAX calculation is being evaluated for the total row and perform a different calculation.

In this blog post you are going to learn how to return blank to eliminate any confusion. In a future blog post you will learn how to use DAX to change the value of the total row.

HASONEVALUE function in DAX

In this simple example, the total row is the sum of all the rows, but this is not what we want. What we would want to show at the total row is how many homes have recently been listed for sale, not the total of all homes that have ever been listed. The first thing we must do is identify if we are at a total row. The way I do this is by using the function HASONEVALUE.

MSDN Definition:

HASONEVALUE: Returns TRUE when the context for the columnName has been filtered down to one distinct value only. Otherwise FALSE.

In the screenshot below I created a new measure called “Totals” and in this measure the function HASONEVALUE is used to correctly identify which row is the total row. This works because at the total level the filter context is all years, so the function returns FALSE.

The final step is to now use conditional logic and replace the Total row with a blank value.

BLANK() function in DAX

New Homes on Market (2)=
IF(
HASONEVALUE(‘Date'[Year]),
[New Homes on Market],
BLANK())

Now the total row is no longer confusing or misleading. In part two of this series I am going to take this a step further and show how to use DAX to return your expected results at the total row instead of just returning blank!

Further Reading: Rob Collie over at PowerPivotPro has a great blog post on HASONEVALUE vs. ISFILTERED vs. HASONEFILTER:
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Thanks!

Quick Tips – Mapping Geography Data in Power BI

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.

1. 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
2. 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.
3. 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

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!

Power BI–Problem, Design, Solution (Text Search)

There is a video that walks you through the solution in depth, please find the video at the end of this post along with the completed solution.

In this installment of the Problem, Design, Solution series we are going to show you how to perform a text search using slicers in Power BI, this simulates a “LIKE” type search. In the following screenshot you can see that when “Tax” is selected all records in the table that have “Tax” anywhere in the record are returned, likewise whenever “IT” is selected from the slicer all records in the table that have IT in them are returned. Hope you enjoy this post!

Problem

Normally slicers (visual filters) only filter records that are an exact match, for example, if you select “IT” from the slicer then you would only expect to see records that have IT and only IT in the record, if it does not match IT exactly then those records are not returned. This is the intended behavior of a slicer and in general, this is exactly what we want. However, there are times when you might want to do a “LIKE” type search similar to the like function in T-SQL. In this scenario, we had a client who needed to do this exact thing. The screenshot below is our simulated problem for this example.

In order to bring back all records that contain “IT” you are required to click each occurrence of “IT” in your slicer, of course, this isn’t a big problem if you have a small number of possible combinations like our sample size. In larger environments, this may be an unreasonable expectation!

Design

Now that we have discussed both the problem and what we want the ultimate solution to look like, it is time to lay out the design. The design includes a four step process.

Let’s break this down a little bit:

• The first step in the process is to identify all the unique values that we need to filter on and put those values into their own distinct table, in this case, we are looking at departments (HR, IT, FIN, Audit, and Tax).
• The second step is to create a measure that uses the distinct departments in our new table as a filter. We will call this measure IsFiltered.
• The third step is to create a new slicer using the values listed in our disconnected table.
• The final step is to tie our new measure (IsFiltered) to the specific visual that we want to be affected by our disconnected table of distinct values.

I would recommend storing this list of values in a file or in a table in a database, for this example I will quickly put these values into a table in my PBIX file. Navigate to the home ribbon and select “Enter Data”, I will name the column Departments and the table Slicer, next I will add the five distinct departments as records for the departments’ column (IT, HR, Tax, FIN, Audit).

Note: This is using the disconnected table design, we are not going to set up a relationship between this new table and any other table in our model.

Step 2 – Creating the IsFiltered measure

This IsFiltered measure is going to give us the “LIKE” type search that we are looking for! Excited yet?? Well, you should be! In this example I am going to use the FIND function, the find function is exactly like the SEARCH function with the key difference being that the FIND function is case-sensitive and the SEARCH function is case-insensitive. The code below will complete step 2 in our design.

IsFiltered =
IF(
SUMX(‘Slicer’,
FIND(
‘Slicer'[Departments],
MAX(‘Department Goals'[Affected Departments]),,0)) > 0,
True“,
False
)

What is this measure doing? The FIND function returns the starting position of the text string we pass in, if the value is greater than 0 then that means that the text exists and this is how we generate a LIKE type search. If the text is not found then nothing is returned and we give that record a default value of 0.

Step 3 – Create a slicer from the disconnected table

Now we can create a slicer from our distinct list of values. At this point the slicer does not filter anything, this is because the filtering occurs through the measure we created in step 2 and needs to be added as a filter to a visual or report in our Power BI Report.

Step 4 – Use the IsFiltered measure to filter a visual in the report

The last step in this process is about as easy as step 3, first we need to figure out which visuals we want to be filtered with the “LIKE” type operation and then we need to use the IsFiltered measure as a filter on those visuals. The criteria here will be to only show the records where the result is “True”. Remember the DAX calculation from above? If a value was found, then a number higher than 0 is returned and we flag the record as “True”!

Finally, I am going to add the IsFiltered measure as a filter to my table visualization, this is done in the Fields section of the visualizations pane. See the below image:

Solution

With the 4 step design completed, the solution is also complete! Now we can apply a filter that does a text search rather than an exact match, see the image below:

Summary

By leveraging the DAX expression language we are able to override the default behavior of slicers and filters in Power BI. Please note that this solution would also work in an SSAS Tabular model, the implementation would be slightly altered as you may be consuming that model from excel rather than Power BI but it would work nonetheless!

Resources

Video of the solution:

Completed Example:

http://tinyurl.com/yd2qon7x