Press "Enter" to skip to content

Category: Power BI

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

Adding KPI Indicators To Power BI

Paul Turley goes on a rant:

Makes perfect sense, right?  This is a basic use case and a good application for simple KPIs; with the one minor caveat that POWER BI DOESN’T SUPPORT THIS!

This topic has become a bit of a soapbox topic for me because it’s a capability that, in my opinion, is a very obvious gap in the Power BI feature set.  After unleashing my rant, I’ll demonstrate a solution a little further down in this post.

Both the rant and the workaround are interesting enough to read, so check them out.

Comments closed

Showing Dataset Parameters In Power BI Reports

Wolfgang Strasser shows how to display the parameters selected for a Power BI report on the report itself:

Dataset Parameters are a way to bring some sort of dynamic into your Power Query datamodelling in Power BI. For my blog post about  Modifying Parameter values in powerbi.com  I was looking for a way to display the value of a parameter inside a Power BI report.

In this blog post I would like to show you the steps that are required to bring your parameters to your field list  – and as a consequence – into your Power BI report.

Read on to see how to do this.

Comments closed

Time Zone Conversion With M

Cedric Charlier shows how to perform time zone conversions with the M language in Power Query:

Everything is fine … except if I share my code with someone from another time zone. The function DateTimeZone.ToLocal is relying on regional settings and in that case my conversion should always be from UTC to “Brussels time”.

I didn’t find any other way to ensure that I’m always converting from UTC to “Brussels time” than implementing the conversion by myself. That’s the goal of the following function

Looks like there may not be a nice “convert to a different time zone” here like lubridate::with_tz() does in R.

Comments closed

Sharing Power BI Content Via E-Mail

Steve Hughes looks at the security implications of being able to share Power BI reports through e-mail:

My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.

Steve shows us where you can go to disable this if you want, as well as places where you can see what content has been shared.

Comments closed