Press "Enter" to skip to content

Category: Power BI

Debugging DAX via Tooltips

Published 2021-01-27 by Kevin Feasel

Matt Allington has a workaround for us:

DAX is a tricky language; on the surface it is deceptively simple, but under the hood it can quickly become complex and it can take many years to master. If you have ever typed a formula and crossed your fingers when you press Enter, then you know what I mean. If you are reading this article, you are no doubt already on your own DAX learning journey.

As you become more competent at DAX, you will start to write more complex formulas that behave differently depending on the filters in your visuals.  One such example is the P&L report that I shared in 2020.  This report contains quite a tricky formula. It was built slowly and methodically, one step at a time, until it was working as desired.  This is the best way to write DAX.  If you want to see how I do it, then go back to the article linked above and watch the video.  While I recommend this as the best approach to writing formulas, the reality is that sometimes you will need to go back to a formula you wrote previously and debug that formula.  Maybe it is not working correctly, or maybe you need to enhance it for some reason.  Whatever the reason, today I am sharing with you my technique to debug complex formulas using tool tips.

Read the whole thing.

Comments closed

Power BI: New Features for Data Analysts

Published 2021-01-26 by Kevin Feasel

Tomaz Kastrun looks at some new functionality in Power BI which might interest data analysts:

Small multiples is a layout of small charts over a grouping variable, aligned side-by-side, sharing common scale, that is scaled to fit all the values (by grouping or categorical variable) on multiple smaller graphs. Analyst should immediately see and tell the difference between the grouping variable (e.g.: city, color, type,…) give a visualized data.

In Python, we know this as trellis plot or FacetGrid (seaborn) or simply subplots (Matplotlib).

In R, this is usually referred to as facets (ggplot2).

Read on for an example of this, as well as two other features, as well as how you might have worked with these ideas in Python and R.

Comments closed

Handling Fuzzy Match Lookup Failures in Power BI

Published 2021-01-22 by Kevin Feasel

Imke Feldmann continues pulling on a thread:

In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?

I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.

Click through to see it in action.

Comments closed

Deciding to Use Dataflows or Power Query

Published 2021-01-22 by Kevin Feasel

Paul Turley explains a choice:

When should you use dataflows vs regular Power Query? I didn’t jump on the dataflows bandwagon and struggled to adopt them at first. Frankly, Power Query is easier to use. The browser-based dataflows designer is quite impressive but it is not as responsive and convenient as the desktop app, so this is a bit of a trade-off. The power and value of dataflows becomes apparently when the business reaches a certain stage of data culture maturity.

Before we can address the question of whether to use Power BI Dataflows, conventional Power BI queries, or any other approach to source and transform data; we need to briefly review different options for orchestrating a Business Intelligence solution in the Microsoft cloud ecosystem.

Read on for a bit of architecture and the explanation.

Comments closed

Writing Calculations on Power BI Real-Time Streaming Datasets

Published 2021-01-20 by Kevin Feasel

Reza Rad shows how we can write DAX measures against a Power BI streaming dataset:

In Power BI, you can have a dataset with Imported data, DirectQuery, Live Connection, or Composite mode. You can build all of those types of Power BI datasets in the Power BI Desktop. However, there is a single type of dataset, which you can only build through the service, called the Streaming dataset.

A streaming dataset is for building reports with real-time response time. For example, if you want to build a Power BI dashboard that shows the room temperature as soon as captured by a temperature sensor. For this type of dataset, you send the data rows using Power BI REST API, which can be called using a custom C# application, or PowerShell scripts, or even from a Power Automate flow process.

Read on to see how.

Comments closed

Using DEFINE COLUMN in DAX Queries

Published 2021-01-19 by Kevin Feasel

Marco Russo and Alberto Ferrari show off some new DAX syntax:

Introduced in December 2020, the DEFINE COLUMN statement lets you define a calculated column local to a query. The column is not persisted in the model, it exists only for the lifetime of the query. Apart from that, it is a calculated column in every sense of the term.

The extension of DAX with the capability to define calculated columns local to a query is needed in order to support composite models over Analysis Services (AS). There are no limitations in the use of the feature. For this reason, you can take advantage of local columns in any DAX query. We refer to calculated columns defined in a query as query calculated columns, or query columns for short.

Click through to see it in action. I like this idea a lot, though do read their note regarding performance, contrasting it with ADDCOLUMNS.

Comments closed

When Power Query Doesn’t Load All Excel Data

Published 2021-01-18 by Kevin Feasel

Chris Webb lets us know about some new functionality in Power Query:

It’s a new option on the Excel.Workbook function called InferSheetDimensions. Here’s what the docs for the Excel.Workbook function say:

Can be null or a logical (true/false) value indicating whether the area of a worksheet that contains data should be inferred by reading the worksheet itself, rather than by reading the dimensions metadata from the file. This can be useful in cases where the dimensions metadata is incorrect. Note that this option is only supported for Open XML Excel files, not for legacy Excel files. Default: false.

Read on to understand when that might happen.

Comments closed

Power BI and Unix Epoch Time

Published 2021-01-18 by Kevin Feasel

Ed Hansberry knows what time it is:

You may need to write a Power BI report that works with Unix Epoch Time, converting either to or from it. Epoch Time is simply the number of seconds that have passed since January 1, 1970, at 12:00 am UTC. So at 1 am of that date, Epoch Time would be 3600, since 3,600 seconds have transpired. Today Epoch Time is around 1610644465, or 1,610,644,465 to make it easier to read, though you will never see the commas in a database.

Converting to and from this time format is deceptively simple, as long as you understand a few functions in Power Query and the concept of Duration.

Read on for a demo.

Comments closed

Calculations in Power BI Streaming Datasets

Published 2021-01-15 by Kevin Feasel

Reza Rad has a workaround for us:

If you use a streaming dataset in Power BI, you cannot download the Power BI file, and you cannot open it using Power BI Desktop. This means that you are limited not to use calculations in a streaming dataset. However, there is a small trick which you can use and can be helpful. I will show you that in this article and video.

Click through for the article, which includes the video.

Comments closed