Trouble with Setting Relationships on a shared Variable

[chris-thompson]

I have a report with multiple data sources which include the following:  a data source of all insurance claims processed; a data source of all users in the system (i.e. people who could potentially receive claims) which includes their current organizational affiliation.  (Those with claims in the first data source may have switched organizations over time, so the same person may have claims in multiple organizations).  Lastly, I have a unique list of all of the potential organizations we serve.

Data Source #1 (all insurance claims processed) includes a field that has an organization ID#, but not the actual title of the organization.

Data Source #2 (unique list of all users in system) also has an organization ID#, but not the actual title of the organization.

Data Table #3 (unique list of organizations) has all organization ID#s and the organization names.

I do have a 1:Many relationship between Data Source #2 and Data Source #1 based on unique User ID.  As there are some situations where we want to calculate the percentage of total users in the system that have claims (Instead of just simply reporting the number that have claims).  

The data in the data sources do have private information, so we want to be able to filter the data based on a specific organization (which I do have a filter setup for doing this).  The only problem is because of the relationship between Data Sources #1 and #2 when I filter for the Organization Name from the unique list of organizations, the data includes claims for a user from when they were with a different organization (in Data Source #1) than the one filtered, because they are tied to the filtered organization in the dataset in Data Source #2 (i.e. their currently active organization).  How would I set the relationships up so that in some situations it would only filter for data which is tied to the user's current organization from the Claims data source, and in other situations we would pull all of the claims data tied to the filtered organization (regardless of where the user's current affiliation lies)?

Here is what the relationships look like between these 3 data sources.

[chris-thompson]

Would anyone be willing to talk this question over with me?  It's much more difficult to explain in text given that I can't share the actual dataset files because of personally identifiable information.  I don't imagine it's all that difficult of a solution for a user who has a lot of experience with Power BI, but I am having troubles explaining it appropriately in this conversation thread without providing context behind the data sources.

[chris-thompson]

More or less I am looking for a way to have a single report-wide filter based on Organization Name.  In the overarching data source with all of the claims, each individual is tied to a specific organization based on where they were when the claim was completed.  Because this data is compiled over time, a single individual may have claims from multiple organizations if they moved organizations within the timespan the data was pulled from.  I have a separate table that lists out each individual and their current organizational affiliation.  I also have one table listing out each unique individual organization in the system.  For some of my reports I would like all data tied to a specific organization (as a year over year report for example).  For these reports it doesn't matter if an individual is currently active in that organization because we want to calculate the total claims for that organization over time.  In other reports, we want to focus specifically on users who are currently active in an organization (i.e. of those currently active in X organization, how many claims does each person have?)  For those types of reports we only want to pull claims data for that specific organization for only those currently active in that organization.  Is there a way I can have a single filter across the entire dashboard that will filter the data based on each of these contexts?  Or do I have to have 2 filters in place?

[lbendlin]

The snapshot doesn't match your description.  Tables #2 and #3 sound like they are dimension tables.  They should filter table #1 (the fact table) but should not talk to each other.

[chris-thompson]

Table #3 could be considered a Dimension table as there is no factual data tied to that one.  Table 1 (the claims table) and Table 2 both could be considered combinations of Dimensions and Factual data.  

[lbendlin]

Hard to propose anything without truly understanding your data and the business scenarios you would like to cover.  I would continue to up the normalization level a little bit, either in your data source or in Power Query.

[chris-thompson]

I'm not really sure how to clarify it further than what I did in my initial post.  I have a dataset that includes all claims (in which there are multiple instances of both users and providers).  I have an entirely separate table of all of the organizations within which the users and providers are a part of.  I have a third table with a unique list of all of the users (students) and their current organization affiliation.  A user (student) may have data tied to more than one organization if they ever moved during the time in which the data was collected (multiple years).  In some situations I want to pull the data for only current users within a specific organization (excluding those users' data when they were at other organizations).  In other situations, I want to include all data from when users were part of the identified organization (regardless of whether they are currently there or not).  Ideally, I would like a single filter instead of multiple to address both of these situations.

[lbendlin]

Would you be able to post sanitized sample data for these three tables?

[chris-thompson]

I have a sanitized data sample.  But I don't see an option to post a file as an attachment on here.