Category: Power BI

Matthew Roche gives us a date dimension:

A bunch of folks from the Power BI community shared their favorite Power Query date dimension queries – you can look at the Twitter thread for links if you’re interested. With their inspiration (and code!) I picked what I liked, discarded what I didn’t, and came up with this:

Click through for that code.

Imke Feldmann has another function to implement:

The Excel NORM.INV function returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. So unlike the NORM.DIST function, that returns the probability of a threshold value to occur under the normal distribution (in CDF mode), this function returns the threshold value that matches a given probability.

Click through for the function definition.

Soheil Bakhshi summarizes the set of reporting options available in Power BI:

Power BI Service

Power BI Service is a SaaS (Software as a Service) offering from Microsoft in the cloud. The users within an organisation, depending on their access rights, may create reports directly in Power BI Service. The users can also securely share and distribute those reports. While creating or editing reports is possible in Power BI Service, it is strongly recommended to avoid this method for several reasons. The most obvious one is that the changes we make to a report may soon get overwritten by someone else that republishes the same report from Power BI Desktop. Check this blog post from SQLChick to see why you should avoid creating or editing reports directly from Power BI Service. The reports are downloadable in PBIX format. 

You can access Power BI Service here.

Click through for the full list and lots of detail.

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.

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.

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.

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.

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.

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.