Press "Enter" to skip to content

Category: Power BI

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

Bulk Removing Reshare Permissions in Power BI

Published 2019-10-08 by Kevin Feasel

Gilbert Quevauvilliers takes us through bulk changing dataset permissions in Power BI:

Whilst this sometimes is what is required, I always suggest to people to remove this option and rather manage it by exception.

If you do leave this option enabled, it means if I had to share a report with Bob, Bob would then be able to reshare the report with someone else. This could potentially lead to other people gaining access to a report or data that they should not see.

Fortunately, I found a way to quickly remove the reshare option in the Power BI Service.

Click through to see how it’s done.

Comments closed

Figuring Dataflow Boundaries

Published 2019-10-08 by Kevin Feasel

Matthew Roche gives some advice on how large to make artifacts in Power BI dataflows:

This post started as a response to this question from Mark, who was commenting on last week’s data lineage post:

How would you decide how big or how small to make each artifact in the lineage, in terms of the amount of transformations taking place inside the artifact? In my case they would only be shared with 2-3 other users.
For instance I could go all out and have every step that would previously take place in a query editor result in a new link in the data lineage chain, but that would probably be overkill.

I agree that “one step per dataflow” would be overkill, but beyond that the answer is largely “it depends.”

Read on to see on which factors it depends.

Comments closed

Dynamically Controlling Power Query Columns

Published 2019-10-07 by Kevin Feasel

Erik Svensen wants to display columns dynamically in Power Query:

This means that even though we might add new columns to the ProductsAttributes table – it will still only be Brand that is expanded and only that column.

The bolded arguments is 2 lists that contains the Column names to expand and the new names of the columns – the last argument is optional so we can actually skip that if we want the original names – https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn

Read on to see how to do this.

Comments closed

Power BI and Case Sensitivity

Published 2019-10-07 by Kevin Feasel

Chris Webb explains that Power BI is case sensitive except where it isn’t:

Case sensitivity is one of the more confusing aspects of Power BI: while the Power Query engine is case sensitive, the main Power BI engine (that means datasets, relationships, DAX etc) is case insensitive. Imke Feldmann shows how to make Power Query case insensitive through custom M here; in this blog post I’m going to concentrate on what case insensitivity means in datasets and DAX and show a way to (kind of) work around it.

Read the whole thing. Chris has a lot of great information here.

Comments closed

Multi-Parent Hierarchies in Power BI

Published 2019-10-04 by Kevin Feasel

Imke Feldmann has another way to solve the multi-parent problem in Power BI:

If you have parent-child-hierarchy with multiple parents, my function will a table like below, where the children with multiple parents still reside in different rows:

Due to this, the table cannot directly be connected with the FactTable, as NodeKey is not unique. Solution is to create DimNode-table that contains only unique values from the NodeKeys. Use it as a bridge between the 2 tables and implement a bidirectional filter to the Nodes-table:

Read on for the complete answer and to grab a copy of the PBIX file.

Comments closed