Press "Enter" to skip to content

Category: Power BI

Creating A Custom Calendar Table With Power Query

Matt Allington shows how to create a calendar table which allows users to set the start and end dates:

My approach to teaching people to use Power Query is to always use the UI where possible.  I first use the UI to do the hard work, then jump in and make small changes to the code created by the UI to meet any specific variations required.  Keep this concept in mind as you read this article.

I am going to use Power BI Desktop as the tool for this, but of course Power Query for Excel will work just as well and the process is identical.  In fact the calendar query at the end can easily be cut and pasted between Power BI and Power Query for Excel.

Check it out for another method for building calendar tables.  I tend to build them in SQL Server because that’s what I’m most familiar with, but it’s good to know a few different ways of doing this.

Comments closed

Using Logical OR Conditions With DAX

Marco Russo shows how to create multiple slicers with logical OR (rather than the default AND) conditions in Power BI and Excel:

Usually, the logical AND condition is the expected behavior for scenarios with different slicers. However, there could be cases where there is a need for a logical OR condition between several slicers. In our example, the semantic would be, “show the quantity sold of products that are of brand Contoso or that have been sold to customers who have a Professional occupation”. The measures OR #1, OR #2, and OR #3 show the same result obtained with three different techniques.

The first two techniques are best practices and might provide different performance depending on the data distribution. The third technique is usually a bad idea because it implements a table filter rather than a multi-column filter. It is included for informational purposes, and as an example of what not to do in production code.

It’s not trivial, but it’s possible.

Comments closed

Substitution Variables In Power Query

Doug Burke gives us an example of here substitution variables make our lives easier in Power Query:

A substitution variable substitutes a variable (a changing value) to get a different result

    a + b = c (where ‘a’ and ‘b’ are substitution variables that define value ‘c’)

  •         If a = 5 and b = 2 then c = 7
  •         If a = 25 and b = 9 then c = 34
 SubVars are especially helpful when
  • you need to move Power Query files from one file path, folder and file name to something completely different
  • you want to use the same PQ reports for different time periods (ie changing months)

It involves looking for changeable items.  If something may or will change in the future, it’s a candidate for a substitution variable.

  • file paths are good for subVars

  • so are time elements such as Year or Month

  • you can also combine subVars where both the file path and month may change

Click through for several good examples.

Comments closed

Migrating Excel Power Pivot Models To SSAS

Imke Feldmann has a walkthrough to show how to migrate a Power Pivot model in Excel into SQL Server Analysis Services by way of Power BI:

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher.

Click through for a list of operations and a video showing how it’s done.

Comments closed

The Most Powerful Force In The Universe, In DAX

Matt Allington talks about compound interest and shows how to calculate it in DAX:

Now back to the point of this article.  Compounding growth is very easy to do in Excel because you can write individual cell formulas (to do what ever you want), and each new formula can reference the answer from the previous formula as the starting point for the new formula. There is no such ability in the DAX language.  To solve such problems in DAX, you have to change the way you think and start to think about how to write a single formula that will work over an entire TABLE of data (or columns or multiple tables) – no cell by cell individual formulas are possible.

Below I will step you through the process of finding a solution to this problem.  As I often mention in my articles, it is the process that I believe is most important.  I seldom know how to answer a complex DAX problem when I start out (that’s the very definition of “complex”), and instead I follow a process to help me solve the problem.  Take a careful read below.  If you apply the same process when you write your formulas, you will be well on your way to becoming a DAX Superhero.

It’s an interesting problem when your growth rate is not always the same, but Matt has you covered.

Comments closed

Expanded Tables In DAX

Alberto Ferrari has a great explanation of how the concept of expanded tables works in DAX:

If you are coming from an SQL background, or if you are used to relational databases, you probably think that RELATED follows relationships. Thus, to compute the Month column, you would think that the engine followed a relationship between Sales and Date and obtained the value of the month by performing a lookup on the Date table.

DAX is different. Date[Month] belongs to the expanded version of Sales, There is a value for RELATED(Date[Month]) because Sales was expanded to include Date using a relationship.
RELATED requires a row context to be active. If you remove the row context of the calculated column, then RELATED no longer works.

This post cleared up a couple of ideas in my head, so check it out.

Comments closed

The Difference Between M And DAX With Cooking

Eugene Meidinger explains the difference between M and DAX as languages using a cooking metaphor:

I like to think of M as this sous chef. It does all the grunt work that we’l like to automate. Let’s say that my boss asks for a utilization report for all of the technicians. What steps am I doing to do in M?

  1. Extract the data from the line of business system
  2. Remove extraneous
  3. Rename columns
  4. Enrich the services table with a Billable / NonBillable column
  5. Generate a date table

This is all important work, but I would have to do the same work for a variety of reports. Many of the steps tell me nothing about the final product. I would generate a date table for most of my reports, for example.

I think the metaphor holds.

Comments closed

Data Cleansing: Hockey Edition

Stacia Varga has a post covering some of the yeoman’s work of data cleansing:

For now, Power BI continues to my tool of choice for my project. My goals for today’s post are two-fold: 1) finish my work to address missing venues in the games table and 2) to investigate the remaining anomalies in the games and scores tables as I noted in my last post.

To recap, I noted the following data values that warranted further investigation :

  • Total Goals minimum of 0 seems odd – because hockey games do not end in ties. I would expect a minimum of 0 so I need to determine why this number is appearing.

  • Total Goals maximum of 29 seems high – it implies that either one team really smoked the opposing team or that both teams scored highly. I’d like to see what those games look like and validate the accuracy.

  • Record Losses minimum of 0 seems odd also – that means at least one team has never had a losing season?

  • Similarly, Record Wins minimum of 0 means one team has never won?

  • Record OT minimum of 0 – I’m not sure how to interpret. I need to look.

  • Score minimum of 0 seems to imply the same thing as Total Goals minimum of 0, which I have already noted seems odd.

This is the kind of stuff that we talk about as taking 80-95% of a data science team’s time.  It’s all about finding “weird” looking values, investigating those values, and determining whether the input data really was correct or if there was an issue.

Comments closed

Ignoring Case In Table.Distinct With Power Query

Cedric Charlier shows how to ignore case when running Table.Distinct in Power Query:

Note that at the top of the table, I’ve twice the value ADO.NET, once with uppercase and once lowercase for the “Net” part. If I try to apply a Table.Distinct, the two values will be considered as distinct and one of them won’t be removed.

Will it be an issue? If this your key and it’s part of a model where this key is part of one side of a one-to-many then it will be an issue.

Read on for the solution.

Comments closed

All About Power BI Licensing

Reza Rad has a guide for the perplexed when it comes to licensing Power BI:

The pricing table above may scare you off and you may immediately think of not going through the embedded path. However, I need to let you know that there are some scenarios which Power BI Embedded can be a much more cost-effective option than Pro. Here is an example:

Assume that you have 100 users for your Power BI solution. And your users are not connecting all at the same time to use Power BI reports. You may have the maximum of 300 page renders per hour for them if you use embedded. In such case, embedded for that scenario would cost you about $700 USD per month, where the Power BI Pro for 100 users would be $1000 USD per month. This means saving of $3,600 USD per year. This is an example scenario that Power BI Embedded can be more cost-effective than Pro.

Give this a careful reading if you’re looking to implement Power BI in your environment.

Comments closed