Press "Enter" to skip to content

Category: Power BI

Using Power BI Cards to Display Notes

Published 2019-10-22 by Kevin Feasel

Prathy Kamasani takes us through one use of the card visual in Power BI:

This is a long-overdue blog post. A couple of months ago, I worked with a client in Amsterdam; one of the use cases was to show key metrics, flags that need attention. The user also wanted to click on warning symbol to confirm, where the issues were, however, the user didn’t want a drill through, it has to be a left-click.

As of yet, except for button/action we can not do left clicks in Power BI. As the user didn’t want the report to open in another browser tab etc., so was thinking about other options and at the end decided to go for tooltips and symbols to show flags like below:

Click through for an example and an explanation of how it works.

Comments closed

Top 5 and All Others in Power BI

Published 2019-10-21 by Kevin Feasel

Marco Russo and Alberto Ferrari show how you can include the top N rows and include an “Others” aggregate at the end:

Power BI offers the ability to apply a Top N constraint in a visual level filter, so that only a certain number of items are visible based on the evaluation of a measure. A common requirement is to show an additional row that accumulates the “other” items, which are those that are not visible in the report like in the following figure.

In order to solve this scenario you cannot use the Top N filter of Power BI. Instead, you apply the filter in a special measure (TopN Sales) and you use a calculated table to accommodate for the additional row named Others. Moreover, you need an additional column to let the Others row appear at the bottom of the table.

Read on to see how you can solve the problem.

Comments closed

Refreshing Power BI Dataflows with Powershell

Published 2019-10-21 by Kevin Feasel

Craig Porteous shows how to use the Power BI Dataflows REST API with Powershell:

I like to use my favourite scripting language to do this – PowerShell. Although we have the Power BI Management PowerShell module (MicrosoftPowerBIMgmt) to interact with Power BI, the cmdlets aren’t yet there to refresh or retrieve the history of a dataflow (or even a dataset) but the module can still help us get what we need without jumping through too many hoops (and as long as we aren’t automating the authentication, that’s another post.).

Click through to see how it’s done.

Comments closed

When Power Query Hits Data Sources Repeatedly

Published 2019-10-21 by Kevin Feasel

Chris Webb answers an age-old question:

If you’re developing in Power BI Desktop and you think that refresh is taking a long time, you should definitely check whether the Power Query engine is hitting your data source more than once. There are lots of ways to do this. Some data sources have tools that show when they are queried, such as the Run History screen in Microsoft Flow that I show in the video or SQL Server Profiler. Other ways include using Fiddler for web services or Process Monitor for files.

Read the whole thing.

Comments closed

Query Folding with Power BI Dataflows

Published 2019-10-17 by Kevin Feasel

Matthew Roche shares a few important points about Power BI dataflows and query folding:

In a recent post I mentioned an approach for working around the import-only nature of Power BI dataflows as a data source in Power BI Desktop, and in an older post I shared information about the enhanced compute engine that’s currently available in preview.

Some recent conversations have led me to believe that I should summarize a few points about dataflows and query folding, because these existing posts don’t make them easy to find and understand.

Read on for those points.

Comments closed

Mapping Usernames within the Power BI Gateway

Published 2019-10-15 by Kevin Feasel

Jeff Pries takes us through a difficult scenario:

With some data sources, such as Analysis Services, you want to pass the username of the person running the report back to the server executing the query (such as in a Row Level Security configuration). Adam Saxton of Guy in a Cube does a great job of explaining how this works at a high level in the video linked here.

In the video, Adam mentions that if our Power BI login does not match a UPN in our local Active Directory, then the lookup will fail…which is a problem if you just don’t have the option of having your Power BI login match a UPN in your local Active Directory and don’t want to manage static user mappings over time. The following will cover a method of allowing the lookup to use a different Active Directory property, such as “mail” to perform the matching.

Read on for the steps.

Comments closed

Power BI Desktop Data Profiling

Published 2019-10-15 by Kevin Feasel

Angela Henry walks through some changes in Power BI:

The Data Profiling feature was first added to public preview just under a year ago in November 2018.  Then it went GA in May 2019 and just 5 months later, they’ve added more goodness.  That’s one of the great things about Power BI, the release cadence.  If you don’t like something or want more features, just wait a few months (or five in this case).

One of the big things that was lacking with the Data Profiling feature was the text length statistics.  This is a huge deal for me.  It’s one of the things that I’ve encounter most frequently, incorrectly sized string columns in data warehouses.  Well, the wait is over, text lengths are now available.  Unfortunately, it’s not intuitive on how to get them.

Sounds like there’s still room for further improvement, but this is a positive step.

Comments closed

Column Masking with SSAS and Power BI

Published 2019-10-11 by Kevin Feasel

Dustin Ryan digs deep into the bag of tricks:

Last week I was asked to tackle a requirement by a customer adopting Analysis Services to enable data exploration and ad hoc analysis by their users. One of their requirements was to secure columns based on a grant related to a cost center. For example, a grant has several attributes, with some attributes being considered “sensitive” and other attributes considered “non-sensitive”. Non-sensitive grant attributes would accessible to all users while a subset of the attributes in the grant table considered “sensitive” would be accessible to users related to the corresponding cost center. The challenge here is that while Analysis Services supports column level security, dynamic column level security is not supported. So my colleague and friend, the great Steve Pontello, and I put our heads together to address the requirement.

Read on to see how they did it and the resulting solution’s limitations.

Comments closed

Automatic Power BI Page Rotation

Published 2019-10-11 by Kevin Feasel

Kasper de Jonge shows how you can make Power BI report pages automatically rotate:

This question comes up pretty regularly, I have a big screen in my hallway and I want to show some Power BI reports that rotate. Now there is some build in functionality in the Windows 10 App for Power BI for it that you can check out here. But there have been some cases where this doesn’t work, like for example if you want your report to run outside of your domain using B2B or when using Power BI embedded that both cannot load the report in the Win 10 app.

For those cases, Kasper has a solution involving an external Chrome add-on.

Comments closed

Sorting by Month in Power BI

Published 2019-10-09 by Kevin Feasel

Cecilia Brusatori shows how you can sort properly by month using Power BI:

Are your visualizations sorting the month field alphabetically? Don’t worry, I’ve been there when I started with Power BI.

If your model has a Dimension Date Table, first congrats, you’re on the right path in what respects to data model, second, you want to use the Month Name from that table, but you noticed that the visualizations don’t sort this column in the way we all wanted, from Jan to Dec.

Click through to see how.

Comments closed