Trending March 2024 # Power Bi Dax Studio Tutorial: Context & The Calculation Engine # Suggested April 2024 # Top 5 Popular

You are reading the article Power Bi Dax Studio Tutorial: Context & The Calculation Engine updated in March 2024 on the website Moimoishop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 Power Bi Dax Studio Tutorial: Context & The Calculation Engine

In this DAX tutorial, we’ll discuss context and its significance to understanding how DAX calculation engine works in Power BI.

The idea of contexts in Power BI isn’t easy and it’s often overlooked. However, we need to remember that we always need to include the concept of context into our minds, especially when dealing with DAX calculations.

Learning and mastering DAX requires a deep understanding of using context in Power BI.

Measures in Power BI need to have an environment in which they do their calculations. This environment is what we call context.

This can be sourced from a variety of locations. Each result in Power BI is calculated independently based on this context.

As an example, let’s look at these visualizations. We’ll use the result 139,152.30.

For this calculation, the initial context is the date. We’re getting this result based on the context it’s coming from, which is the 27th of September 2023.

Thus, dates in tables are one of the places that a context can come from.

A date slicer can also be used as the source of the context in our reports.

A slicer provides a date range which can further drill into specific dates based on a selection. So, it’s creating an encompassing context on our report page.

Other than dates, we can also use products as our context. To demonstrate, let’s select products in our slicer.

The selections we made are creating context for the results in the table.

We can see that the value for the 27th of September has become 64,661.70 because of the context created from our data and products.

Moreover, we can also create context from the Customer name slicer.

Even if we change the context we’re using in the selections, the Total Sales measure will always calculate the same way.

In Power BI, we can write simple formulas and promptly get a huge number of results through slicer selections because of the context we layer on these codes.

It’s very impressive in terms of speed. We can quickly achieve results with basic formulas and changes to the context.

We’re not only making a selection when using a slicer; we’re also changing the context of the outcome.

Slicers are not the only means of doing this. We can also change the context by making selections within charts.

In the example below, we can look at our Sales by Channel using the donut chart.

This is how we get the result of 40,052.60 for the same date in 2023.

A filter is positioned over tables every time we make a selection in our reports.

If we select products, for instance, this will reflect in our Products table. Then, a process called filter propagation happens in the formulas.

Let’s look at this using a model perspective.

Our Products table has a “one to many” relationship to our Fact table, Sales.

The Customers table also has this similar relationship.

And since we have filters for our dates, this means that we also have a relationship between the Dates table and the Sales table.

There’s also another filter coming from the Channel column in our Sales table.

These filters create the context we place in our reports. The selections are streaming down to the Sales table, filtering the results based on the selections made in slicers and charts.

Furthermore, another important technique we can use is having a Customer Name Index in the Sales table.

This can substantially reduce an 8,000-row table to a few hundred depending on the context we apply on the results.

The Total Sales formula can also produce the same outcome; however, it will only work within smaller subsets of the original table.

I hope that you get a lot from this DAX tutorial. This is quite helpful especially when you’re just starting with Power BI. Understanding DAX context how the DAX calculation engine works is an integral part when learning Power BI.

Regardless of the data scenario, DAX calculates and provides results based on the context and filters applied in the report.

So, always remember this concept for every result that appears in Power BI models.

All the best,

Sam

You're reading Power Bi Dax Studio Tutorial: Context & The Calculation Engine

Power Bi Reporting Templates Expanded – Power Bi Visualization Concepts

In this tutorial, I dive into how to customize Power BI reporting templates efficiently. You may watch the full video of this tutorial at the bottom of this blog.

When you create tables inside Power BI, you can’t format or design them exactly the way you want them to be. You can only use the default order or sequential order that Power BI generates within the table, which can be determined by a value, by alphabetical order, or an ascending or descending order.

That’s why I developed Financial Reporting Templates. These are a development concept created by Enterprise DNA. With this technique, you can now create customized financial reporting templates using tables inside Power BI.

This tutorial session is taken from a much longer and more detailed Enterprise DNA member-only workshop. Here, I demonstrate key concepts around how to visualize financial information and data effectively in Power BI.

First, let’s have a look at this template that I made for financial reporting.

Financial reporting is a perfect example for this demonstration. However, there’s a lot of custom templates or custom tables that you might want to create with a variety of information, such as a summary table of your key metrics.

In the past, this was very difficult to do if you wanted to grab specific measures out of different measures and integrate them into one report and put them on top of the other.

Now in this sample template I’ve broken it down by Total Revenue, Cost of Goods Sold, and even more, which is where the true customization comes in. I’ve left a space in the table, then I have Total Gross Profit.

I’m integrating a percentage with dollar amounts as well, which is again with a customization.

Let’s get to how I created it…

To get to this point of having to customize Power BI templates, it requires a lot of re-configuring in the background, the model set up, and the technique to grab revenues and costs, and then integrate them into one.

The first thing to do is to actually create the template – a unique table that looks somewhat like this – but of course you can customize in whatever way you want. You can put different columns or have some items indented in a different way.

You also need a Normalized Column, where we’re going to run a logic through. We’re going to identify which line we’re on in our custom template based on this normalized column. 

So this is the set up of a customized template, but then you need to find a way to integrate this into your report. This is where the data model comes in.

The structure of the data model is key to integrating your customize template into your table. As you can see in my data model, the Financials Template is right at the bottom and it has no relationship to any other tables.

This is where I put my template column and use a formula to work it out at every row in the table.

So let’s have a look at the DAX formula I created to solve this.

I used the SWITCH TRUE logic in the formula, which enables me to integrate the customized template that I made into the table. It works out at each row in the table, identifying the items.

This is what the SWITCH TRUE does. For instance, if I’m on the Total Revenues row, the calculation DIVIDE Revenues by 1,000 is applied. If I’m on the Total Cost of Goods Sold row (COGS), it will show DIVIDE Revenues by 1,000 etc.

It’s the SWITCH TRUE logic that enables the allocation of results to the rows in the table.

The final piece of the puzzle for this Power BI template technique is the Row Index, which sorts out the template in the table.

If you don’t have this, it’ll get confusing and won’t sort the items in a unique way. And what I did was, I hid it so you can’t see it in the table, but it’s actually there.

If you look closely, you can see that I have the Row Index and I’m able to sort 1, 2, 3, 4, 5, and so on. That’s how I get the exact template set up as I want.

This tutorial is a breakout session from a members-only Enterprise DNA workshop around financial insights. Here I focused on the technique that I developed to customize Power BI reporting templates. The technique involves the integration of data model, DAX formula, and template creation.

I hope you able to understand how valuable this concept is and implement it in your own business. You will find it helps a lot with your financial reports and any accounting presentations you might have.

Cheers!

Sam

Handling Http Errors In Power Query And Power Bi

If you are working with web servers, either because you are trying to scrape data or you are using a web based API, you will be sending and receiving data via HTTP.

HTTP is the Hypertext Transport Protocol – it’s just the name of the system used by web sites to transfer data. You use it every time you visit a web site

If your request results in an error, the web server (or API) will generate an error which is returned to Power Query.

The default behaviour is for Power Query to then spit out a message like this

If you are not familiar with HTTP or this type of error then this can be confusing. What exactly is the problem?

This Works In Power BI and Power Query in Excel

I’m going to do this in Excel but you can do the same in Power BI. The main query uses exactly the same code in Excel and PBI. But the method to create the static data table of HTTP Error Codes is different.

In Excel I use #table and in Power BI I use the Enter Data button on the Ribbon. To read more about the different ways to enter static data check out this blog post Static Tables in Power Query, Power Pivot and Power BI.

Watch the Video

Download Sample Files

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Power BI Desktop File.

If you could handle this type of error in your code and provide a little more information to the end user, perhaps that would help them troubleshoot the issue and resolve the problem.

What if your error message was this

The message gives the user some idea of things to check (spelling) and tells them a way to confirm the URL is correct (type it into the browser).

Looking at the query, the URL it’s trying to access is

Typing this URL into my browser gives this error

You may have already spotted that the URL is incorrect. It should end with microsoft-365 not microsoft-356

By giving a more informative error message and some steps the user can take to troubleshoot, we can help them fix, or at least understand problems that may occur.

Manual Status Handling

Manual status handling means you are going to write your own code to deal with errors. You have to tell Power Query you’re going to handle them and you do this by specifying the ManualStatusHandling value when you make the Web.Contents request.

Web.Contents

Only Web.Contents allows you to manually handle HTTP responses. Neither chúng tôi or Web.BrowserContents support this ability.

For example to tell Power Query that you will deal with 400 (Bad Request) and 404 (Not Found) errors the request would look like this

Where the list of error codes you’ll handle are specified as a list of numbers {400, 404}

Handling errors means that you need to write you own error messages. To store these I’ve created a static data table that stores the error codes and the messages I want to display should the associated error occur.

The table is stored in a query called HTTP_Errors and looks like this

The ErrorCode column is a list of the error codes I’m handling so I can change the Web.Contents request to reflect this by replacing the { 400, 404 } list.

To check if an error has occurred you can use the Value.Metadata function

This gives you data like this, and I’m interested in the Response.Status

You can see the web server has responded with a 404 error. To access this value directly you can do so like this

Now that we can get the response code from the web server, we need to check if it is an error we want to handle. To do this you can use List.Contains to check if the ResponseCode is in the ErrorCodes column of the HTTP_Errors table.

If the web server’s response code is an error we want to handle then the code needs to display the associated error message.

To access the error message, first I’ll use List.PositionOf to get the row number for the error code.

Because table columns are lists you can use list functions on them. Lists are indexed from 0 so error code 404 is on row 3.

If the web server’s response is not an error I want to handle then the code will just return the response as it is.

Putting this all together the code looks like this

If an error occurs that isn’t listed in my HTTP_Errors table then Power Query will deal with that in the default way.

If no error occurs then the Response step contains the web server’s response and further transformations can be carried out on it.

Current Trend Analysis In Your Power Bi

In this unique example, I want to show you how to project a historical trend in Power BI and repeatedly project it forward. This particular example came from a question in the Enterprise DNA support forum. You may watch the full video of this tutorial at the bottom of this blog.

An Enterprise DNA member needed to carry out this analysis in the real world using historical information, which could be for example, quarterly or half-year results. They then had to take that older information and project them forward for some years.

This is an excellent example of how you can reuse time intelligence functions in many different ways.

You can also enable this logic to be filtered by a particular region, a specific store or for a particular customer set.

The visualization for your trend analysis is practically useful when you have a grouping, a quarter, or a sales period and you want to project or predict the results going forward.

This solution that I have discovered can be applied in a number of different ways. The current example may be about predicting a quarter, but you could project anything continuously going forward using a similar technique.

There’s a lot of interesting ways to apply a trend analysis inside your Power BI report. Now, take a look at this table below.

Firstly, you can see the columns for the specific dates of the year and its corresponding total sales. But, I also need to showcase what day it is in a particular quarter. That is why I created the Quarter Day column. 

I’ve gathered that data from the Dates table. In this table, you can see the Quarter Day column. Let’s see how I worked this out.

A quarter day is a significant figure in business. It signifies the day that officially begins a three-month period of the year (a quarter). It is important to really work out this dynamic calculation of quarter day since I intend to project its trend every single quarter in every year.

So, in order to find out the quarter days, I used the formula below.

It’s just going to find any particular date, and then subtract it to the very start of quarter. Lastly, just add 1.

As I go down the Quarter Day column, you’ll see that it continues to go all the way up until about 92. And then, it goes back to 1 at the very first start of the next quarter. 

Now that I have calculated the quarter day numbers, I need to decide on the particular time frame that I want to project forward. To do that, I need to use the formula below for the Quarterly Forecast.

First, I use the CALCULATE function for the Total Sales.

The FILTER function removes ALL the filter in the Dates table initially, and then re-applies them to a very specific time frame, which is 2024 for year, quarter 3 for quarter, and the very same day of a particular quarter.

The MIN function actually evaluates to the quarter day of every single day as we move through any month into the future and evaluates their correct quarter day. 

So in any particular day in the future, it will always jump back to the 3rd quarter of 2024, on the same quarter day.

In this example here, I’m showing a very specific time frame instead of just repeating historic numbers. In the example visualization below, I’m actually comparing the quarterly forecast to the total sales.

The graph seems a bit busy so you can actually improve it a little bit. This is just one way to see it in a comparison perspective. 

Aside from that, you can also use a moving average measure inside it. This can help users to calculate moving averages of a specific time frame, and then just continually project that out.

Moreover, you can predict anything out aside from sales and that’s where measure branching comes in. Actually, you can also add factors and even percentages for your particular results.

To sum up, all these trend analysis can be done very efficiently within Power BI when you set up your calculations correctly.

The great thing about this technique is that I feel this is such a real-world application in Power BI. Likewise, it’s also about how efficiently you can create this sort of logic using DAX formulas. Moreover, you can showcase it in dynamic ways when projecting your trends forward.

Good luck with learning this one.

Sam

Sorting Date Table Columns In Power Bi

In today’s blog, I’ll discuss a question that comes up all the time in the Enterprise DNA forum. How can you can sort the fields from an extended date table columns properly? You may watch the full video of this tutorial at the bottom of this blog.

This refers to the extended date table from the M code showcase that was developed by Melissa Dekorte, one of our Enterprise DNA experts.

Our example on this blog post is just a simple application that runs from 2023 to 2023 with a fiscal year that starts in July. The member wanted to learn how to sort the Month Name; they were also trying to sort one of the fields related to month.

This is how you can sort a date table column effectively.

Our extended date table has a lot of fields in it; frankly, they’re hard to keep in order sometimes.

This is why we created this cheat sheet that shows you what each of the fields looks like, as well as their format and content, on any given day.

In most cases, you can use this to figure out how to sort one field based on another.

We have the Month Name and the forum member was trying to use MonthnYear to sort it out.

Let’s find Month Name in our extended date table. Select Month Name in the Fields pane, and then go to Sort by column, and look for MonthnYear.

This comes down to a granularity problem. You need a one-to-one relationship between the field you’re trying to sort and the field you’re using to sort by.

In this case, for a given month in 2023, 2023, and 2023, we can have three different values for the MonthnYear field. This is why we need to find one that has a one-on-one relationship.

If we go back to our cheat sheet, we can see that MonthofYear is just the number of the month. This will have the one-to-one relationship that we’re looking for. This means January will correspond with 1, February with 2, and so on.

The next one, Month & Year, is a little more difficult. It is a compound field that has a short month and year.

You can see that for the Month & Year table column, everything has sorted out perfectly.

So now we have the first two done. However, the last one is a really ill-behaved field.

It’s problematic for a couple of reasons. We have two text fields concatenated together and the fiscal month field is not padded.

Ultimately, we want to achieve something similar to this MonthnYear, where we have a four-digit fiscal year and then our two-digit fiscal month padded after that.

The easiest way to do this is by using the power query.

We go to Transform data.

And then we go to our Dates table and add a Custom Column.

Let’s call this custom column Fiscal Year Fiscal Month Sort (FYFM Sort).

We’ll start with our text prefix which is 20 for the first two digits of our year and then we’ll concatenate that with a function called Tex.Middle. This function pulls a substring out of a larger text string. It’s analogous to the DAX function MID, and I’ll show you the difference between the two.

We’re going to use this on our fiscal year field.

In DAX, when we pull substrings, it’s a one-based index. To pull the third character, we will need to use 3. In power query, it’s a zero-based index so the third character will be 012.

We start with 2, which is the third character. We want to return two characters to get that second pair of digits off the fiscal year field.

The next step is to concatenate this with our padded fiscal month field.

In DAX, we will need to use a LENGTH function and IF function, then we can manually pad.

In power query, there’s a function that does all of that. This is the Text.PadStart.

Since the fiscal period is a numeric field, we’ll need to use a function called Text.From to convert the numeric value into a text value that we can pad. Then we’ll pull our fiscal period field.

We want to make sure that it has 2 characters. In cases where it has 1 character, we’re going to pad it with a 0.

If we sort our FM & FY field by column, it will now sort perfectly by fiscal year and then a fiscal month.

Conclusion

In this blog, I’ve covered the technique on how to sort date table columns in Power BI. This is a good strategy to do for difficult fields, which came from building a few custom functions. I hope you have found this general set of tools helpful in your own report development.

If you haven’t done so already, please subscribe to the Enterprise DNA TV channel, where we have more content coming out in the future.

Cheers!

Sam

Power Bi Challenge 22: Hotel Revenue Management

There were so many amazing submissions in the recently concluded Enterprise DNA Challenge 21 that we’re excited to see what everyone comes up with as we start the next round.

Out of those 62 entries, we received submissions from 44 non-subscribers and 40 newcomers. These numbers are proof that you don’t have to be a Power BI expert to join these challenges. Quite the opposite, in fact. If you’re just starting out in your journey, the best way to learn is to dive in to real-life datasets and get help from the seasoned pros in our community forum.

This time around, we’ll find out how we can use Power BI to improve hotel revenue management. So if you’re an analyst working for the hospitality or tourism industry, now’s your time to shine!

To refresh your memory, the Power BI Challenge is a series of fun and friendly challenges that all started in our Power BI forum. For each challenge, the Enterprise DNA team releases a scenario simulating what you are likely to face in a particular industry.

The objective is to assess this scenario and using the dataset provided, come up with an actionable report that provides insights and solutions that meet the requirements set out in the brief. The entire community will then come together to review the project and discuss solutions on the forum.

We have covered a lot of real-life scenarios from various industries and niches ranging from Formula 1 to COVID-19 reporting. The sheer amount of support and participation we get for every challenge we post is a huge testament to how flexible and dynamic Power BI truly is.

Here are the categories of winners for the Power BI Challenge 22:

Experts & Previous Winners – entries from the Enterprise DNA experts and previous winners of the challenges

Overall Winner – all entries are eligible, excluding experts and previous winners

First Time Participant Winner – open to any Enterprise DNA subscriber who is taking part in the challenge for the first time

Winning Non-Member – open to entries who are not currently Enterprise DNA subscribers

Out of the Box Thinker – for those submissions that dig deep into their analysis

Creative Head – for those who bring a lot of effort into their report and analysis

The overall winner gets a free Enterprise DNA On-Demand subscription, a chance to be featured at the Enterprise DNA Showcase page, as well as a chance to present their findings in one of our webinars. All category winners also stand to get free subscriptions to the Enterprise DNA On-Demand platform.

And even if you don’t win any of these categories, you still have a lot to gain from joining the challenge:

Get your work showcased across the Enterprise DNA network

Receive personalized feedback on your development

Get experience across different industries

Build an awesome analytics portfolio for prospective employers

If you want to start working on your submission, check out the details for Power BI Challenge 22 here. Revenue management involves the use of data analytics to help those in the hotel industry predict their customers’ behavior. The resulting insights are then utilized to make better decisions in regards to pricing, marketing, and distribution strategies.

Your role as a data analyst is to prepare a deep-dive analysis on the hotel revenue management dataset provided in the brief.

Your report will not be limited by the number of pages or techniques. Feel free to use all the techniques at your disposal – tooltips, drill throughs, page navigations, etc.

Also take note of these client requirements when making the report:

Client does not want an Intro Page but suggests having vertical/horizontal navigation bars or pop-up menu.

No scrollable or fancy shape reports. Preferred size is 16:9.

Client likes KPIs.

Client is very much interested in a time analysis exploration (Seasonality, Festive Periods, Weekday vs Weekend).

Client is interested in an agent performance overview.

Client is interested in customer type information (family with children, single, or couple visitors).

Email the pbix file to [email protected]

Include a brief description of the report in your submission.

If you’re a subscriber, post the screenshot and the Publish to Web URL of your report in the Enterprise DNA forum.

If you’re not a subscriber, post the screenshot and the Publish to Web URL of your report in LinkedIn.

Tag Enterprise DNA and say, “I accepted the Enterprise DNA challenge.” Use the hashtags: #EDNADataChallenges #EDNAHotelRevenueManagement in the social media post.

Update the detailed information about Power Bi Dax Studio Tutorial: Context & The Calculation Engine on the Moimoishop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!