Working with Averages in DAX using AVERAGEX, SUMMARIZE and DATESINPERIOD

In this blog post, we are going to dive into the world of averages and how to solve problems when calculating averages by using CALCULATE, SUMMARIZE and AVERAGEX. Specifically, we look at creating an average calculation across months and as you will see in the video below, this can be a challenge on multiple levels!

Working with SUMMARIZE and SUMMARIZECOLUMNS in DAX

Our goal in this video is to return the average sales for the last 3 months, see Figure-1 below.

SNAGHTML9292404

Figure 1 – Average of last 3 months of sales

Our first attempt at solving this problem may be to just use the expression AVERAGE([Sales Amount]) as seen in Figure-2 below. However, the AVERAGE function will first return the AVERAGE of all the transactions for the month and therefore the results of the following calculation are not quite as expected:

image

Figure 2 – Average of Sales Amount, incorrect calculation.

The result of the expression in Figure 2 can be seen in the following screenshot. Ultimately, average is returning the SUM of the Sales Amount divided by the number of transactions for each month.

SNAGHTML92fc901

Figure 3– Average of Sales Amount

Now that we understand this behavior and what is occurring, we can take a direct approach at solving the problem. First, we need to create a virtual table that contains the months along with their total sales. In order to create this aggregated table we can use SUMMARIZE or SUMMARIZECOLUMNS. In this scenario I will use SUMMARIZE because SUMMARIZECOLUMNS doesn’t quite work correctly within a modified filter context and we will need to change the default filter context to get 3 months in our aggregate table.

The following expression will return an aggregate table with the Year, Month and Total Sales.

image

Figure 4 – Creating a virtual, aggregated table with SUMMARIZE

SUMMARIZE is a function that will return a table expression and therefore you would get an error message if you tried to just put this expression in a calculated measure. However, you could test this out or “DEBUG” this expression by putting it in a calculated table or running the expression from Dax Studio. See the results of this expression from Dax Studio below in Figure 5:

image

Figure 5 – A view of the virtualized table created by SUMMARIZE

Working with AVERAGEX, CALCULATE and DATESINPERIOD

There are a few challenges still left to solve to get our desired result. First, you can’t use the AVERAGE function on a table or table expression, you must provide a column and therefore the result produced by SUMMARIZE here cannot be used by AVERAGE. However, X Functions like AVERAGEX accept a table or table expression as their first parameter and therefore work perfectly with this virtual table.

The next challenge is that the virtual table is returned within the current filter context and therefore we would always be returning the AVERAGE of the current month and only the current month. UNLESS, we use the CALCULATE function to evaluate the expression within a modified filter context, which is exactly what we would do! The calculation in Figure 6 below is the expression that returns the desired result seen in Figure 1.

SNAGHTML94d9dd9

Figure 6 – Final calculation

Dynamically changing title names in Power BI

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!

Original on Transparent

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.

DynamicTitles

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:

image

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:

image

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:

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

As always, hope this post helped!

2_Conditional Formatting


Introduction to Wrangling Data Flows in Azure Data Factory

Hello! It’s been a while since I’ve done a video on Azure Data Factory. To get back in the flow of blogging on ADF I will be starting with Data Flows, specifically Wrangling Data Flows.

The video can be seen here:

What are Wrangling Data Flows in Azure Data Factory?

Wrangling Data flows are a method of easily cleaning and transforming data at scale. huh?

Wrangling Data Flows uses the M query language and the UI experience provided by the Power Query Editor in Power BI Desktop. This is a brilliant move by Microsoft to include this technology in Azure Data Factory. Just think of the hundreds of millions of people who currently are transforming and cleaning their data in Excel or Power BI Desktop. Now they can take their self service ETL (extract, transform and load) skills to the enterprise level with ADF.

What makes it scalable? Power Query Editor at Scale.

Wrangling data flows allows the developer to use the graphical user interface to do all the hard work with minimal to no code. But in the background all of your UI steps are being converted to the M language. At runtime, Azure Data Factory will take that M code and convert it to Spark and then run your data flow against big data clusters. This means as your data volumes grow, you should experience consistent performance!

Are there any limitations with Wrangling Data Flows?

Yes… quite a few actually. Wrangling Data Flows are still in preview at the time of this blog and the related YouTube video. Currently there are quite a few operations that just aren’t supported. The most obvious of those operations being promoting header rows and pivoting data. I hope that these features will be available once the product is in GA.

https://docs.microsoft.com/en-us/azure/data-factory/wrangling-data-flow-functions#known-unsupported-functions9

As always, thank you for reading my blog and watching my YouTube videos! Have a great day!!

Other Azure Data Factory resources!


ALL vs ALLSELECTED in DAX and Power BI Desktop

Hey all! In my latest YouTube video  on DAX I discuss how ALLSELECTED can be leveraged in Data Analysis Expressions (DAX). This continues a series of YouTube videos in which I discuss various DAX functions and how to use them in Power BI Desktop.

Please feel free to check out my YouTube channel and subscribe Winking smile at https://www.YouTube.com/c/MitchellPearson

If you want to watch the video of this function explained, please take a look below:

ALLSELECTED function in DAX and Power BI Desktop

First, let’s look at the definition of ALLSELECTED, provided inside Power BI desktop when referencing the function:

image

ALLSELECTED: Returns all the rows in a table, or all the values in a column, ignoring any filters that may have been applied inside the query, but keeping filters that come from the outside.

What does this mean? Well, it means that it will essentially ignore filters coming from that visual but still respect filters coming from slicers and filters outside that visual (the outer query)! And yes, this is pretty awesome as you will soon see.

ALL vs. ALLSELECTED DAX functions

The biggest difference between ALL and ALLSELECTED is that the ALL function ignores all filters, regardless of where they are coming from. In contrast, the ALLSELECTED function only ignores filters that are coming from the inner query.

Returning Total Sales of all Selected Countries

The following expression in DAX would return the total sales for all countries in all scenarios. For example, the countries Australia, Canada and France were selected in the slicer, but yet the measure Total Sales All Countries still returns the total sales across all countries:

image

Figure 1.1

In this scenario, you might want to return Total Sales for all countries selected in the slicer (allselected), $13,682,863.16. This can be quite tricky, especially if you didn’t know of the existence of the ALLSELECTED function. Let’s write the expression in Figure 1.1 a different way and look at the results.
image

Figure 1.2

In this example you can clearly observe that ALLSELECTED is providing different way to analyze your data. I love this dynamic approach to analyzing data that is provided by simply modifying the ALL function to use ALLSELECTED!

As always, I hope this blog has helped someone somewhere and thanks for reading my blog!!

Using R in Power BI to check if file exist

I have done a series of blog posts on how R can be used in Power BI. I have also created quick videos for each of those posts and I will do the same for this post. I find it easier and more efficient to share this information via video than through a narrative with screenshots. In this blog post, I’m going to share the code that can be used to check if a file exist prior to processing of that file. I will record a video that goes in more depth and include that in the blog post later.

Check if file exist

As someone who comes from an enterprise BI background, I am always looking for ways to handle things I would have previously done with SSIS. With R integration we have a lot of new possibilities.

The Code:

fileexists <- FALSE
fileName   <- “C:\\Backup\\Blogs\\R – Check if File Exist\\CustomerSales1.csv

while (fileexists == FALSE)
    {
   #fileexists = TRUE ##Test Expression
   fileexists <-
            if(
                file.exists(fileName))
               {TRUE} else {FALSE}
   print(fileexists)
   ## Add 3 second Delay
   Sys.sleep(3)   
    }
   
    data  = read.csv(fileName)
    head(data)

PASS Summit–Lightning Talk on R

I’m excited to have the opportunity to speak at PASS Summit 2018. I am doing a quick 10 minute lightning talk on R integration with Power BI. Somehow I was able to sneak in with this incredible group of speakers! SHHHHH, don’t tell anyone lest they find out!

Speakers

I have loaded my slide deck here, this includes what you need to get started with R! Slide Deck

In this talk I am focusing on how R can be used to extend the capabilities and functionality of Power BI. More specifically, I will be using R to show the following four demos:

  • How to extract data from Power BI using R
  • How to unzip and process files with R
  • How to Download, unzip and process files
  • How to check if file exist before processing the file

Also, I have created blog posts with corresponding YouTube videos already on 3 of the 4 demos listed above!

YouTube

My YouTube channel is simply MitchellSQL

Blog: MitchellPearson.com

Below are the three blog posts that I have previously posted:

Export Data from Power BI using R

Unzip and Process files into Power BI with R

Download, unzip, and process with R

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.

image

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

image

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:

https://www.ssa.gov/oact/babynames/state/namesbystate.zip

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:

image

  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 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:

image

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!

image

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:

https://docs.microsoft.com/en-us/power-bi/guided-learning/

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

https://devinknightsql.com/category/power-bi-custom-visuals/

PowerBI.tips – Custom Theme Generator

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

https://powerbi.tips/

DAX Guide

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

https://dax.guide/

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