Press "Enter" to skip to content

Category: Power BI

Counting Employees by Period with DAX

Published 2022-05-24 by Kevin Feasel

Matt Allington solves a common problem:

I’m calling this article, “How many employees by period”. Staff come and go for different reasons. In some companies, the number of staff can change over time. The principles used in this article can also be used in other instances.  There can be staff moving in and out of departments, on and off of projects, etc. The technique can also be used to work out how many staff were on leave, how many off sick, how many tickets were open in a support queue, or any other concept that has a start and end date in a transactional table.

Read on for Matt’s answer but be sure to check out the comments as there are some other good solutions in there.

Comments closed

Object-Level Security in Power BI

Published 2022-05-23 by Kevin Feasel

Chris Webb checks out Object-Level Security:

If you have sensitive data in your Power BI dataset you may need to stop some users seeing the data in certain columns or measures. There is only one way to achieve this: you have to use Object Level Security (OLS) in your dataset. It’s not enough to exclude those measures or columns from your reports or to hide them, because there will always be ways for enterprising users to see data they shouldn’t be allowed to see. However the problem with OLS up to now is that it didn’t play nicely with Power BI reports and so you had to create multiple versions of the same report for different security roles. The good news is that there’s now a way to create one report connected to a dataset with OLS and have it display different columns and measures to users with different permissions.

And then watch as Chris combines Row-Level Security with Object-Level Security to make it nicer for users but probably a mess for maintainers.

Comments closed

Forcing Color Scheme by Data Element

Published 2022-05-23 by Kevin Feasel

Reza Rad forces a particular color scheme in Power BI:

You can set the color in every visual in Power BI easily. You can also set the color of multiple visuals at the same time using Themes. However, what if you want to set the same color for the same data point? For example, You want the Gender Female to be always colored Orange in all the charts and visuals. In Power BI, as of now, you cannot set a data point color. However, there is an easy solution for that, which I explained in this article and video.

Click through for the answer. Generally I’d say something along the lines of “instead of doing this, just have one color and take advantage of cross-filtering to highlight the element people care about.” But if you do have a multi-measure categorical set with a small number of categories, color can be a differentiator and at least this helps you keep consistent colors across visuals.

Comments closed

Power BI Field Parameters

Published 2022-05-19 by Kevin Feasel

Matt Allington throws one in for free:

The May 2022 version of Power BI Desktop includes a very interesting and useful feature – Field Parameters. Today I will show you how to use this new feature illustrating with 3 (no, wait, 4) use cases – Chart Elements, Chart Axis, Table Contents and Permanent Ad hoc Hierarchies.

Read on to see how to use this preview feature in Power BI.

Comments closed

Managing Power BI Tenant Settings

Published 2022-05-18 by Kevin Feasel

Melissa Coates takes us through tenant settings management in the Power BI Service:

The tenant settings in the admin portal of the Power BI Service are incredibly important. The tenant settings include a wide-ranging number of things that significantly affect the user experience. It’s really important to manage the tenant settings effectively.In this post I’m going to talk about the process you should go through for reviewing and specifying your tenant settings.

The following is a high-level overview of what’s involved:

Read on for a helpful image as well as a flow of what to think about before you act.

Comments closed

Adding Dynamic Hierarchies in Power BI

Published 2022-05-18 by Kevin Feasel

Kristyna Hughes makes use of the Tabular Object Model:

Power BI hierarchies are a powerful and easy way to enable end users to dig deeper into their visuals and data. While hierarchies can be a useful resource for self-serve analytics, maintaining hierarchies can be a pain as new levels get added or removed. Thankfully, if you have Power BI premium you can use the XMLA endpoint to add code into existing .NET applications to dynamically add or remove levels from hierarchies as they are created/removed in your application.

Unfortunately, while we can manipulate, add, and delete hierarchies and their levels, visuals already containing the hierarchy will not be automatically adjusted with any new levels/ordinals.

In spite of that limitation, click through to check out what you can do.

Comments closed

Returning an Empty Table in Power Query

Published 2022-05-18 by Kevin Feasel

Imke Feldmann doesn’t want you to leave empty-handed:

When your query returns an error, sometimes it could make sense to return an empty table instead of a simple error message. And ideally, this table has the same columns and types than the table that would have been returned without the error. Check out Chris Webbs article about it here: Chris Webb’s BI Blog: Handling Data Source Errors In Power Query Chris Webb’s BI Blog (crossjoin.co.uk)

Read on for a function which generates an empty replica table based on some other table.

Comments closed

Reviewing the Gartner BI Magic Quadrant

Published 2022-05-16 by Kevin Feasel

Matt Allington takes a look:

I update this blog article regularly to keep track of how Microsoft is progressing on its strategic journey in building a world class BI platform.  Gartner released the 2022 magic quadrant for Business Intelligence in March 2022.  As expected (by me at least), Microsoft is continuing its trail blazing and has maintained a clear lead over the other contenders in both ability to execute and completeness of vision.  I first wrote this blog article in 2019 to show the trend over time. Since then, I have been collecting the new Gartner magic quadrant images and I have combined them all (2015 – 2022) into an animated gif file (see below).   The visualisation was built using Power BI (what else) so it is very easy to see the changing nature of the competitive environment.  Note that Microsoft started behind back in 2015, and arguably hit the lead in 2018.

Also check out Matt’s video on how to translate spatial values to numbers so Power BI can plot them.

Comments closed

Generating a Report for Known Issues in Power BI

Published 2022-05-12 by Kevin Feasel

Gilbert Quevauvilliers builds a report with daily alerts:

I was actually working on my own version of the Known Issues Power BI report, when the Power BI team released their blog post New Power BI Known Issue page | Microsoft Power BI Blog | Microsoft Power BI

In the steps below I am going to show you the report I created, and then how I configured it to alert me when there are updates to new known issues.

I also subscribed to this report on a daily basis, this allows me to quickly and easily be aware of any known issues in Power BI.

Read on to see how Gilbert did this and click through for a copy of the report for your own purposes.

Comments closed