Several relationships between tables (I need to use columns, not measures for USERELATIONSHIP)

[mateus_luzzi]

Hello, specialists, how are you?

I'd appreciate your help to solve a problem that I'm facing.

I have 25 imported tables, gathered by client's MongoDB. This means that I need to set everything on PBI, I can't manipulate data in that DB.

Then, in my report, I have 6 pages, and every page need a specific relationship between those tables, in order to properly work. That relationship isn't about measures, cause I know I could use USERELATIONSHIP to calculate or do something else with specific relationship. The problem is that I need to manage relationships using column's, simplifyng: 

Imagine that I have 4 column's in table A:
ID, ClientID, Company, Title

And 4 columns in table B:

ID, ClientID, Company, Title

In the first report page, I need to create a visualization that shows the company (from table A) and the title (from table B). The relationship used by this visualization must be ID.Table A = ID.Table B

In the second report page, I need to create a visualization that shows the same thing, company (from table A) and the title (from table B), but the relationship used, must be: ID.Table A = ClientID.Table B to properly work.

I've crated several virtual tables from the root table (I mean the physical table that I imported) to create a relationship between each one to use them specifically when I need. For example, I duplicated Table A and Table B and created both relationships that I said before, and used each one for each report page. The biggest problem is that now I have 74 tables, between physical and virtual tables. The visualizations aren't working properly because of a possible relationship issue. 

Does somebody have an idea of what can be done? 

Thank you very much!

[mateus_luzzi]

@PaulDBrown, yep, that's make sense of course, but that's not the problem I'm facing. I've done this data modelling just because I didn't know another way to use different and also active relationships at same time for the same tables. I need a way to use the same column of a table with 2 or more relationships, at same time, each relationship for each report page.

@BA_Pete Hi, Pete. I cannot use USERELATIONSHIP because, as far I know, it can't be applied to calculated columns, only scalar measures. And I really need to use the columns, because I don't want to return a scalar, but a entire column. For example, I need to display in a matrix Companies by it's Titles, using a different relationship between companies' tables and titles' tables each report page. The link that you brought enlightened me, but I didn't fully understand. Using EVALUTE + CALCULATE + ADD COLUMNS will bring a calculated column inside the original root table, or it will create yet another virtual table?

Thank you!

[PaulDBrown]

@mateus_luzzi 

Can you provide a mockup PBIX with  data and an example of what you are trying to achieve?

No need for real data; just a sample of tables involved (easily done in Excel), and a depiction of what you need. It would help immensely 

[mateus_luzzi · Accepted solution]

Hi, @PaulDBrown. 

I really appreciate your enthusiasm and support to help me get out of this situation. In the middletime, my boss managed to solve this by his own. Since he was working on this project way before than me, he tried another method. He simply (not so simple) get the raw data imported to PBI and created a 'master' table, joining every table as possible for each report page, resulting in a 'big joined table' for each report page, then he used that 'master' table to create the visuals. I think this is a possible solution for people facing the same problem as I did.

This extends to @BA_Pete.

Once more, thank you both for you support. Keep your good job helping novices to PBI, we appreciate.

[PaulDBrown]

@mateus_luzzi 

Best practices recommend setting up dimension tables with unique values between fact tables with fields in common in one-to-many relationships. You then use the fields in the dimension tables in your visuals to retrieve  fields from the fact tables. Make sense?

[BA_Pete]

Hi @mateus_luzzi ,

Can you expand on why you don't want to use USERELATIONSHIP?

Your scenario sounds perfectly suited to this technique i.e. set two relationships between tables:

TableA[ID] > TableB[ID] (active)

TableA[ID] > TableB[Client ID] (inactive)

In your first scenario, you could just drag in the [Company] and [Title] fields and these would work fine based on the active relationship.

In your second scenario, you would use a measure something like this:

_tableBTitleReln2 =
CALCULATE(
  MAX(TableB[Title]),
  USERELATIONSHIP(TableA[ID], TableB[ClientID])
)

If you really want/need to bring columns across into other tables, rather than calculating values like above, then maybe THIS THREAD will help?

Pete