Why Is My Relationship Inactive in Power BI Desktop?


The Power BI Desktop tool now attempts to utilize bi-directional relationships when it can. If any ambiguity exists, some of your relationships may become inactive. If you are seeing inactive relationships (the dotted line), take a look in Advanced properties to see if the “cross filter direction” is set to “Both” rather than “Single” for some of the relationships. This is particularly true if you have multiple fact tables in your dataset – it’s recommended to stay away from bi-directional relationships if dimension (lookup) tables are shared across fact tables. Although the “cross filter direction” property allows many-to-many relationships and offers more flexibility with respect to filter propagation, my approach is to use bi-directional filtering sparingly and only when a specific analytical need has been identified.

How Power BI Desktop Handles Relationships

After doing some experimentation, reading, and talking with people, I've learned these important things:

  • Power BI Desktop will auto-detect relationships when it can (ex: matching column names and data types). According to this Power BI Support article“Cardinality, Cross filter direction, and Active properties are automatically set.”
  • Power BI Desktop won’t necessarily create all relationships. If it doesn’t have a very high confidence what to do, it won’t create the relationship.
  • Power BI Desktop will attempt to set the cross filter direction to “Both” whenever it can – that’s the new default behavior.
  • Power BI Desktop may set a relationship to inactive if there is some ambiguity present.

So, be sure review all of the relationships in your dataset, and don’t rely entirely on the auto-detect functionality. 

How to Create Relationships in Power BI Desktop

There are three ways to create relationships in Power BI Desktop:

My preferred method for creating a relationship is to use the “Manage Relationships” pane. This allows me to specify the column from each table, as well as the items under Advanced options including cardinality, cross filter direction, and if it’s active or not. 

Second method is to drag one column to another in the Relationships pane. This way sets all of the advanced options to its default settings. Since I have very specific preferences for the Advanced options, I tend not to use this alternative as often.


Third method is to use the Autodetect functionality within the Manage Relationships window. This can be a time-saver, but you still need to verify each one is doing what it’s supposed to do. Therefore, I tend to lean towards creating them manually so that I’m certain the data model is specified exactly the way I want it. Since Power BI Desktop does Autodetect by default, you probably won't need to use Autodetect unless you've deleted some relationships along the way.


What are Bi-Directional Relationships in Power BI?

Bi-directional relationships have a few aliases. People refer to it as as BiDi, cross filtering, and/or many-to-many.

When set to “Single,” the dimension (or lookup, from the ‘one’ side of the relationship) can filter the fact (or base table from the ‘many’ side of the relationship), but it cannot filter the other way around. That's why the arrow points one direction. In the following screen shot, this means we can slice and dice the Quantity measure (from the fact) by Customer (from the dimension). For example: Sum of Quantity by Customer.

This works well for most scenarios when a traditional dimensional model (aka star schema) is implemented, wherein dimensions contain attributes and facts contain measures. This facilitates the slicing/dicing in the dimension and aggregation in the fact. 


Conversely, bi-directional relationships permit filters to propagate in Both directions (as opposed to one-way with the Single filter). Under the covers, for filtering purposes, Power BI Desktop treats the two tables as if they’ve been flattened into one single table.

With the standard “Single” direction relationship as shown above, we could *not* do something like Count of Customers by Quantity sold – that type of question is moving the aggregation to the dimension and the slicing/dicing to the fact which is the inverse of what we usually do. However, the Both setting allows that type of analysis to work.


Sounds like a great idea. Why wouldn’t we want to always do this?

When to Avoid Using Bi-Directional Filtering in Power BI Desktop

If you have more than one fact table in your dimensional model, you want to stay away from the ‘Both’ setting. I don’t claim to understand entirely how it works under the covers, but I can confirm that you end up with some inactive relationships that you really want to be active. As you probably know, an inactive relationship can only be invoked through a DAX calculation so that limits some self-service BI scenarios.

We have a project with a traditional star schema with 4 fact tables and approximately 15 dimensions (about half are actually role-playing date dimensions). Each relationship from dimension to fact represents a standard 1-to-many. After migrating the existing model from XLSX into Power BI Desktop, I was surprised to see approximately 5 of the relationships were set to inactive in Power BI Desktop. Here is a vastly simplified version of that data model (reduced to 2 dimensions in this example) to illustrate what I was experiencing:

See in the above screen shot how some of the relationships are inactive? That’s because Power BI Desktop detected some ambiguity. Since some of the ‘Both’ cross filter settings were allowing bi-directional relationships, that caused issues when trying to create relationships which otherwise are absolutely valid and active. This Power BI Support article states: “If you have two or more tables that also have lookup tables (with some in common) then you wouldn't want to use the Both setting.” So, my general rule is to avoid using the ‘Both’ setting when any dimension (lookup) tables are shared among multiple fact (base) tables.


Also, be aware that since the bi-directional ‘Both’ setting does alter how filters work, more complex queries may be sent to the data source. So be sure to test for both performance and accuracy, and that row level security does what you expect it to do. The kind of testing we always do anyway, right? Right.

In conclusion, I would like to propose that bi-directional cross filtering should be used in moderation – If you have a specific analytical need, and if there is one single fact table sitting in the center of dimension (lookup) tables then it should be just fine to use. If you see some unexpected behavior, or you see inactive relationships that you don't expect, the relationship settings are one area to check.

Finding More Information

Power BI Support:  Create and Manage Relationships in Power BI Desktop

Chris Webb Blog: Bi-Directional Relationships and Many-to-Many in the Power BI Designer

      and One-to-One Relationships in Power BI

You Might Also Like...

Groups in Power BI - How Sharing and Security Works

Direct Connect Options in Power BI for Live Querying of a Data Source

How to Stop an Azure Virtual Machine on a Schedule

As you've probably heard, Azure Virtual Machines can be stopped when not in use in order to save subscription costs. My personal Azure VMs are stopped more than they're started because they're my personal sandbox/learning areas. I've set up Azure Automation to stop my VMs each evening so I don't have to rely on memory.

The technique I'm describing below is a little different than what Dustin Ryan blogged about - I've chosen to use a credential instead of a certificate since I've got Azure Active Directory running in my environment (though I do get plenty of notifications about unhealthy synchronization attempts since my VMs are off!). It does require an account to be a co-administrator in Azure; if you're uncomfortable with that, take a look at the certificate approach Dustin talks about.

1. AD User.  First, set up a domain user in Active Directory. I chose to call mine AAutomation. Since its purpose is for Azure Automation, it doesn't need to be a member of any particular groups in AD; mine is just in the Users group.  The user ID and password will be stored in an Azure Automation Credential (step 5 below), so handle your password expiration setup (or use of an MSA) as you see fit. 

After it the sync runs between AD and AAD, you're ready for the next step.

2. PowerShell Script. Next we need a PowerShell script to perform the shutdown action. Create your script and store it in a .ps1 file for when we need it in a later step. Here's what my script looks like for shutting down four VMs. Note you can also use a "*" to shut down all VMs which would prevent future maintenance of the script. That would work for me, however, I am a co-administrator of another "real" environment in which we have certain VMs that stay up 24/7; therefore this example specifies which VMs should be shut down (one of those cases where I used my own sandbox to test prior to putting into the real environment). Also notice that this PowerShell script references the Azure Automation account we created in step 1.

workflow StopAzureVM {
    inlineScript {
        $Cred = Get-AutomationPSCredential -Name "AAutomation@YourDomainNameHere.com"
        Add-AzureAccount -Credential $Cred
        Select-AzureSubscription -SubscriptionName "SubscriptionNameHere"
        Stop-AzureVM -ServiceName "ResourceGroup1NameHere" -Name "VMName1Here" -Force
        Stop-AzureVM -ServiceName "ResourceGroup2NameHere" -Name "VMName2Here" -Force
        Stop-AzureVM -ServiceName "ResourceGroup3NameHere" -Name "VMName3Here" -Force
        Stop-AzureVM -ServiceName "ResourceGroup4NameHere" -Name "VMName4Here" -Force

3. Azure Co-Administrator. Go to Settings in the Azure management portal. Add your new AAutomation account to be a co-administrator.

4. Azure Automation Account. Go to Automation in the Azure management portal. Create a new Automation Account. I called mine AAutomationAccount to coincide with the naming convention for the User AD account. It'll take just a few moments for it to be created.


5. Automation Credential. Click the name of your new Automation Account to go into its properties. Go to the Assets page where you'll see some predefined items. Click Add Setting. Here's where you want to Add Credential. The type will be Windows PowerShell Credential (rather than a certificate). In this dialog box you will specify the user and password that were set up in step 1.


6. Import PowerShell Script. Go to the Runbooks page and choose Import. Here we want to import the PowerShell script created in step 1. 


7. Publish Runbook. After the page refreshes, click the name of your Runbook to go into its properties. Go to the Author page. If Azure converted any of your syntax (i.e., to a workflow), you'll see some comments about that. Notice at this point that the script is in draft status. At this point you can test the script. When everything looks ok, Publish the script so that it can be scheduled.

8. Set up Schedule. Go to the Schedule page and choose Link to a New Schedule (unless you have an existing schedule that is). Specify the timing that works for you. Once it's set up, you should see it on the Schedule page. You can also visit the Dashboard page for other information about the Automation job status.

That's it for the steps to schedule Azure VM(s) to shut down on a schedule. 


Overview of Power BI V2 Features End-to-End

Back when we were using Power BI for Office 365 (V1), I did an end-to-end feature diagram. Since the first one was popular, here's an updated diagram for the "new" Power BI (V2):


Note the above is accurate to the best of my knowledge as of the date in the top left of the image. For instance, integration with the Azure Data Catalog isn't available yet, nor is the Enterprise Gateway, but these features are coming. The pace of change is fast, so please be sure to verify.

I've also posted a 5-minute video tour of this diagram, which can be found here:  Tour of the Power BI Ecosystem.

You Might Also Like...

Ways to Utilize Power BI in a Bimodal BI Environment

Direct Connect Options in Power BI for Live Querying of a Data Source

Groups in Power BI - How Sharing and Security Works

This past week, groups in Power BI came up multiple times. This post is to give a quick overview of what you can and cannot currently do with groups in Power BI V2. This info is as of mid Sept 2015; features and functionality are evolving fast and furious so be sure to verify at a later date.  

**Updates to this post since it was originally published mid Sept 2015:

  • 9/23/15 due to release of new Group sharing functionality
  • 10/23/15 due to release of read-only members within Groups
  • 11/1/15 due to addition of support for sharing to Active Directory security groups

In V1 of Power BI, I was a big fan of using SharePoint Online sites and document libraries to organize content by subject area and/or serve as a security boundary. Groups are the replacement in Power BI V2 for organizing content and/or securing content for groups of users. Since groups represent an organizational feature, they're only available to Power BI Pro users (not the free version of Power BI).

What Are Power BI Groups?

Groups in Power BI are not really "just" Power BI Groups. Rather, they are Office 365 unified groups which can be used across a number of apps. The type of groups we'll see exposed in Power BI are similar to distribution groups (but a little different from distribution groups & security groups). A new O365 unified group gets a group e-mail account, file space in a OneDrive location, shared calendar, and other collaboration features. You can set up Azure Active Directory Sync (AADSync) to synchronize on-premises AD group members with Office 365 group members.

The first thing to be aware of when evaluating use of groups is that there are multiple types of workspaces in Power BI:  My Workspace and Group Workspace. Each type of workspace can contain its own datasets, reports, and dashboards. As you would expect, each user has only one My Workspace whereas there can be numerous Group Workspaces.


To see Groups in the Power BI web portal, click the arrow (chevron) at the top left. It points up or down depending on whether the group list is expanded or not.

Using the + sign you can create a new O365 unified group for use in Power BI.

Each group that the logged-in user is a member of will appear in the list of group workspaces.

How Does Sharing & Security Work with Groups in Power BI?

First let's talk about sharing out of My Workspace. You can share a dashboard with one or more coworkers out of your personal workspace. Note that you cannot share reports or datasets from My Workspace.

Within a Group Workspace, however, you have two choices:  

  • Share the dashboard with a recipient. Just like in My Workspace, sharing a dashboard is a read-only for the recipient. The recipient will see the shared dashboard within their own personal workspace.  Or,
  • Add a user as a member of the group. The group members will view the content within the group itself (rather than in their personal workspace).

Therefore, the Group Workspace offers a lot more flexibility than dashboards shared out of My Workspace because group members can utilize dashboards, reports and possibly datasets depending on member permissions. 

For all members in a group, the member permissions can be set to either:

  • Members can edit Power BI content, or
  • Members can only view Power BI content

Notice in the following screen shot how the privacy setting for edit vs. view applies to all members in the group. At the individual member level, I can specify an individual to be Member or Admin. However, what I cannot do is specify the type of member, edit vs view, at the individual user level.


Be sure to devote some time to planning how you want security of groups to work as it relates to delivering content organized by subject area, topic, user base, etc.

What Group Members See in Power BI

For members in a group that are set to view only, note how they don't see the Datasets, nor is the Edit Report menu option enabled:


Alternatively, as you expect, for members with edit capabilities, they see everything in the group, and have the ability to edit all objects in the group:


Ways to Publish Read-Only Content in Power BI

To summarize overall options, there's four ways I'm aware of to share read-only content:

First option is to share dashboards via your personal workspace. That ensures the recipient does receive a read-only copy. In this situation, only one person (the original author that did the sharing) can make edits. **Note: this is not a great practice for critical reporting - if the original author leaves the company, it is a hassle to reset the password and get into their account to retrieve original items. It's better to use groups for housing original content that is important to a number of users.**

Second alternative is to share dashboards via group workspace. That ensures the recipient receives a read-only copy. Any group admin or members with edit permissions can edit the original content.

Third way is to add "view only" members to a group. In this case, users will go to the group to view content, rather than their My Workspace. This is helpful when you want to organize content across subject areas. In this situation, only group admins will be able to edit the original content (because *all* members will be view only).

Fourth option is to publish the content via an organizational content pack. Individual users can use the "Get Data" functionality to discover the content. They'll bring the dataset / report / dashboard into their own My Workspace. If the user wishes to make changes, Power BI will prompt them to create a second (personalized) copy. This is a good option, but two copies could get confusing for some users. Also, this isn't a big deal, but it does put the "burden" on individual users to go out and find the content via Get Data.  Neither are deal breakers, but something to be aware of depending on the user base. 

A Few More FYIs About Using Groups

The view only setting for group members is only acknowledged inside of Power BI. Which means the view only permissions does *not* translate to files in OneDrive for that O365 unified group. Specificially, this impacts Excel files that are uploaded to OneDrive so that Power BI can connect to them and launch an Excel Services window for viewing. As other report types continue to be integrated with Power BI (such as SSRS), it'll be interesting to see how this evolves.

Also, it appears that groups created outside of Power BI don't have the capability enabled to set view-only vs edit permissions for the members. So be sure to keep an eye on this as you're setting things up. If you can create the unified group directly in Power BI, that seems to work most seamlessly.

After a new group has been created in Power BI (or People or OWA), it will appear in the Office 365 Admin Center (you need to have Office 365 administrator privileges in order to get to this area). 

I noticed that groups created directly in the O365 Admin Center Groups pane don't show up in Power BI. At first I thought that was a problem. However, after a bit of research, I understand that it's because when using the O365 Admin Center Groups pane (shown in the screen shot above) it's created as a security group rather than the new-fangled O365 group. However, if you create a group through the People section or OWA or Power BI, it'll be created as a the type of group which can be utilized in Power BI like we expect.

Also, I've not had a chance to test this but I've read that if you have any policies set up (for group naming, mailbox policies, etc), the Power BI group creation process isn't yet aware of those policies. Since by default any user can create a group, some system administrators have set up policies to get around this to avoid disorganization.

You Might Also Like...

Ways to Utilize Power BI in a Bimodal BI Environment

Direct Connect Options in Power BI for Live Querying of a Data Source