Category: Power BI

Imke Feldmann has a formula for us:

The Black Scholes formula returns the value of European put and call options. The version I’m sharing here uses the standard normal cumulative distribution function from my previous blogpost.

Click through for an R version followed by the same function in M. I was going to comment on how much more code the M version was, but about half of that difference was Imke kindly adding in documentation and the other half was the inclusion of the normal CDF generator. Otherwise, it’s roughly the same number of lines.

Comments closed

Paul Turley is excited about the latest Power BI update:

The December 2021 Power BI Desktop update introduced a long-awaited upgrade to the partitioning and Incremental Refresh feature set. The update introduces Hybrid Tables, a new Premium feature that combines the advantages of in-memory Import Mode storage with real-time DirectQuery data access; this is a big step forward for large model management and real-time analytic reporting.

Read on to see why.

Comments closed

Nicky van Vroenhoven wants to clean up some text:

Just before the holidays start, I’d like to share this little trick I came across in Power Query.

I recently had the need to remove zeroes (but it can be any character) from the beginning of a column.
Turns out there’s a perfect function for that 🙂

Click through to learn what this function is and how it works.

Comments closed

Imke Feldmann goes searching for a function:

If you’re looking for Excel functions in Power BI, you will recognize that much more of them have been transformed to native DAX functions than to native M functions in Power Query. One of the basic statistical functions that hasn’t made it to M is the NORM.DIST function.

Click through for an M function which approximates the Excel function.

Comments closed

Chris Webb points out something of interest:

If you’re using Power BI in DirectQuery mode against a SQL Server-related data source (ie SQL Server on-prem, Azure SQL DB or Synapse) you may have noticed a new feature that was released a few weeks ago: query labels that allow you to link a SQL query to the Power BI visual that generated it.

There’s nothing you need to do to enable it – it happens automatically. Here’s an example of a TSQL query generated by a DirectQuery mode dataset with it in:

Though I’m not sure if on-prem SQL Server or even Azure SQL Database has a nice way of viewing data by label. I know that dedicated SQL pools do and they’re a rather helpful method for figuring out who (or what) is doing work.

Comments closed

Adam Aspin takes us through an important topic for paginated report developers:

In the previous article of this short series, you learned the fundamentals of creating datasets using DAX to populate paginated reports delivered using the Power BI Premium service. The next step is to appreciate the practicalities – and subtleties – of how data can be filtered using DAX for paginated report output.

As most, if not all, report developers come from an SQL background, it may seem overkill to devote an entire article to filtering data. However, DAX is very unlike SQL as far as filtering output data is concerned. Something as simple as classic OR logic needs to be handled differently from the techniques you may be used to – either as a SQL or as a Power BI developer. To ensure that you can deliver the report data that you need to populate paginated reports, take a detailed look at how to filter data in DAX datasets using the core SUMMARIZECOLUMNS() function.

Read the whole thing.

Comments closed

Matthew Roche explains a bit of jargon:

What I am going to do here is talk a little about “desktop hardening,” which is a term I’ve heard hundreds of times in meetings and work conversations, but which I discovered is practically undefined and unmentioned on the public internet. The only place where I could find this term used in context was in this reply from Power BI PM Christian Wade… and if you don’t already know what “desktop hardening” is, seeing it used in context here isn’t going to help too much.

When I hear that term, I definitely do not think about APIs; instead, the first thing which comes to mind is security, which is a totally different story.

Comments closed

Chris Webb builds a particular kind of test:

As a postscript to my series on Power BI refresh timeouts (see part 1, part 2 and part 3) I thought it would be useful to document how I was able to simulate a slow data source in Power BI without using large data volumes or deliberately complex M code.

It’s relatively easy to create an M query that returns a table of data after a given delay. For example, this query returns a table with one column and one row after one hour and ten minutes:

Read on for a version of the function which slowly emits rows, as well as some T-SQL which slowly emits rows.

Comments closed