Create a relationship between tables where any of 2 specific columns values may match the original

[GabrielFLima]

I have a report that basically drills down a table based on the selection (you select a person and all the stuff 'owned' by this person which are in other tables is shown). For this, I had some one-to-many relationships set between the person's name in the original table and the 'owner's' name in the tables which have the content.

It worked just fine, but recently there's been a change and the 'stuff' can belong to 1 or 2 people, which means that I can no longer look at only one field to determine if that row interests me or not.

Basically I'd like PBI to retrieve all rows from those auxiliary tables where ANY of the 2 'owner' columns contain the name of the selection.

I've been looking around for quite some while and haven't found someone with a similar question, so I hope someone can give me a little hand here with what I could try since I'm kind of stuck 😛

Thank you all in advance!

Edit: Adding some sample data so my problem can be better visualized:

I have a Ranking table which contains these key fields;

Ranking

Name	Points
Person 1	100
Person 2	80
Person 3	60
...	...

And a few "auxiliary" tables with the documents sent by each employee. These tables are all modeled like this:

Auxiliary

Doc. Owner 1	Doc. Owner 2	Doc. Name	Points
Person 1		Doc 1	50
Person 3	Person 1	Doc 2	30
Person 2		Doc 3	90
...		...	...

As I said, I already have a relationship set between these tables from the time there could only be 1 owner per document (also there are many Auxiliary tables):

Ranking               Auxiliary

 Name       1:*     Doc. Owner 1

The Ranking table is what is shown to the user, and by hovering over each person's row the amount of documents sent by that person is shown as a tooltip.

The problem is that when there's more than one owner, I get forced to pick which auxiliary column I'll be using for the relationship, and because of this the value shown will be wrong. For example, for the values given, Person 1 should have 2 docs, but the report will only show 1.

Hope things are clearer now.

[lbendlin]

This is easy to do with a custom column generator function.

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[GabrielFLima]

Thanks a lot!

I edited the post to include sample data (even though my tables' formatting is a little off), did it get better?

[lbendlin]

Can the format of your auxiliary table be changed?  Generally you want to unpivot such tables before using them in a data model.

[GabrielFLima]

Yes it can, it's a Sharepoint list where the documents are added and the points are given by some flows, and I have full access to it since I'm the one developing it.

Now what exactly do you mean by 'unpivot' the tables? English isn't my first language, so the only thing I relate it to are Excel's pivoted tables.

[lbendlin]

You would go from 

Doc. Owner 1	Doc. Owner 2	Doc. Name	Points
Person 1		Doc 1	50
Person 3	Person 1	Doc 2	30
Person 2		Doc 3	90

to 

Doc. Owner	Doc. Name	Points
Person 1	Doc 1	50
Person 3	Doc 2	30
Person 1	Doc 2	30
Person 2	Doc 3	90

[GabrielFLima]

Ok I can see this working being formatted like this, what should I look into to make this transformation directly on Power Query?

Unfortunately I can't make this kind of change directly on Sharepoint since it would create some other problems, specially for consulting such documents in the future.

I did some research on pivotting/unpivotting tables in Power Query but still haven't found a similar result...

[lbendlin · Accepted solution]

Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lFSAGKX/GQw29RAKVYHLmsMFEFSCFJkBKSNURQZIRkB0mAJlI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Doc. Owner 1" = _t, #"Doc. Owner 2" = _t, #"Doc. Name" = _t, Points = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Doc. Name", "Points"}, "Attribute", "Doc Owner"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Doc. Name", "Points", "Doc Owner"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Doc Owner] <> " "))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[GabrielFLima]

My man you have no idea how much you helped me, I can't thank you enough!! 😄

Btw I found out that if instead of "Unpivoting other columns" you choose to unpivot only the selected columns, it doesn't create rows with empty "owners", and the "filtering" step is no longer needed.

[lbendlin]

oh really?  I need to try that. Cheers!