Effective Identities And Power BI Embedded

Angela Henry shows how you can use Power BI Embedded for row-level security even when the accessing users don’t have Power BI accounts:

Now that you familiar with Row Level Security in Power BI, how do you make it work when you want to pass in your customer’s identifier because your customers don’t have Power BI accounts?  It seems like the only way to make dynamic row level security is to use the Username() DAX function?  But wait, doesn’t that require the user to have a Power BI account?  Sigh, it seems we are going in circles.

The one thing these articles don’t talk about is that when you are using Power BI Embedded, you can pass in whatever you like for the EffectiveIdentity via the Power BI API and it will “overwrite” the Username() function.  What?!  That’s right, it will completely ignore the Username() function and use whatever you give it.  WooHoo!

Read on for the details.

Using Profiler To Get Power Query Timings

Chris Webb shows us how we can combine DAX Studio with Profiler in order to time our Power Query operations:

And there you have it, exact timings for each of the Power Query M queries associated with each of the tables in your dataset. Remember that the time taken by each Power Query M query will include the time taken by any other queries that it references, and it does not seem to be possible to find out the amount of time taken by any individual referenced query in Profiler.

There is a lot more interesting information that can be found in this way: for example, dataset refresh performance is not just related to the performance of the Power Query M queries that are used to load data; time is also needed to build all of the structures inside the dataset by the Vertipaq engine once the data has been returned, and Profiler gives you a lot of information on these operations too. 

Check it out if you do any work with Power BI.

Power BI Workspace V2

Reza Rad shows us the differences between Power BI Workspace V1 and V2:

Workspace version 2 has been available in Power BI Service for more than 6 months now. The new version introduced in August 2018, however, still many people don’t know what it is, and what is the difference of that with the old version, and the most important question: Should you create the new workspace in V2 or V1? Should you click on the Try Now button when you create the new workspace or not? I have previously written about workspaces and their important role in creating a collaborative environment. In this post, I’ll answer all questions above to help you make the right decision when creating the workspace. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

I’ll admit I was unaware of V2 workspaces. This was interesting reading.

Hiding Future Dates In DAX Measures

Marco Russo shows how we can define year-to-date measures which don’t include values for incomplete months:

The rows between September 2009 and December 2009 should not be visible. The goal here is to display a blank value in these out-of-range, “future” months.
A similar issue exists for the year-over-year calculation (YOY). Even though the measure tries to show a blank value in case of missing values in current or previous year, the amounts for August 2009 and for CY 2009 might be considered wrong.

The answer is certainly not trivial but it does make for a much nicer display.

Power BI Palette In Charticulator

David Eldersveld has submitted a pull request for Charticulator:

This morning, I submitted a pull request on GitHub to bring the default Power BI color palette to Charticulator. While Charticulator can export a Power BI custom visual .pbiviz file, there are no color-related Format options in Power BI. Any color choices must occur in Charticulator before exporting (you currently cannot change them later in Power BI). As a result, having the Power BI default palette easily accessible in Charticulator could help bring more consistency between the two tools.

I think this is a good idea. Consistency between products allows people to combine them more effectively.

Adding A Column With Custom Function Code In Power Query

Imke Feldmann has a nice step-by-step tutorial on adding a new column in Power Query which uses custom function code:

The following steps show how to create a new column in a table using existing custom function code. This works in Power BI as well as in Power Query in Excel:

The guide is entirely screenshot-driven, so it’s easy to go through.

Orphaned Workspaces In Power BI

David Eldersveld explains what orphaned workspaces are in Power BI:

One of the newer features in the Power BI Admin Portal is the ability to view all of a tenant’s Workspaces. As I was browsing through the collection of workspaces, I noticed several marked as Orphaned. What is an orphaned workspace, and how does it occur?

I was expecting orphaned workspaces to be a new thing where you pay for an Azure service using a distributed blockchain technology called Gruel (or maybe Grool).

Stream Analytics And Power BI

Brad Llewellyn gives us a demo on connecting a Stream Analytics stream to Power BI for data analysis:

We understand that streaming data isn’t typically considered “Data Science” by itself.  However, it’s are often associated and setting up this background now opens up some cool applications in later posts.  For this post, we’ll cover how to sink streaming data to Power BI using Stream Analytics.

The previous posts in this series used Power BI Desktop for all of the showcases.  This post will be slightly different in that we will leverage the Power BI Service instead.  The Power BI Service is a collaborative web interface that has most of the same reporting capabilities as Power BI Desktop, but lacks the ability to model data at the time of writing.  However, we have heard whispers that data modeling capabilities may be coming to the service at some point.  The Power BI Service is also the standard method for sharing datasets, reports and dashboards across organizations.  For more information on the Power BI Service, read this.

Brad has a nice demo, so check it out.

Daylight Savings Time Calculations In Power BI

Fred Kaffenberger shows us how to convert UTC to local time zones with daylight savings time:

Quick tip for DST Refresh Date function Power BI Service. I’ll put the code up front, and explain it below. I’ll also say a bit about how to use it at the end. The United States and other places, like Australia, have a pesky thing called Daylight Savings Time. This means that in Central Time US, the offset from Universal Time Coordinated (UTC) is sometimes -6 and other times it’s -5. While Power Query can convert time zones, it doesn’t handle DST. And, my users like to see when the reports were refreshed as a step in evaluating data quality. In 2019, US DST is from March 10 – November 3 (2 AM local time). So, the functions here need to be updated every year.

As promised, here’s the custom function. 

Click through for the custom function and a nice explanation of how it works.

Copy Measures Between Power BI Files

Matt Allington shows us how you can copy measures between PBIX files:

Warning!
Ok, here is the warning.  This is not supported by Microsoft.  If you do this and it breaks your model, you will not get support from Microsoft (or me for that matter).  So, back everything up and keep the backups – don’t delete them.  Consider yourself suitably warned 🙂 .

This warning just makes me more likely to do it…

Categories

March 2019
MTWTFSS
« Feb  
 123
45678910
11121314151617
18192021222324
25262728293031