Relation problem with tables already linked to consolidated tables (ambiguity between tables)

[Anonymous]

Hi everybody,

I am facing a relationship problem between two tables already linked to consolidated tables.

I am trying to make a report listing both alerts and incidents datas.

The context is the following : 

I have a first table (lets call it table A) containing datas about servers alerts :
- I have a date column
- I have an owner column
- and an incident number column

I have a second table (lets call it table B) containing informations about incidents :

- I have a date column
- I have an owner column
- and an incident number column

I created two consolidated tables :

- a date table named Calendar

- and a table named Owners witch contains uniques values

(I created theses two tables to use them as slicer in my report, in order to filter datas on both tables)

table A (alerts)
- is linked to Calendar table with a many to 1 relationship
- is linked to Owners table with a many to 1 relationship

table B (incidents)
- is linked to Calendar table with a many to 1 relationship
- is linked to Owners table with a many to 1 relationship

Until this point my report works fine (I can filtrate on both tables by date and owner)

Here is my problem : I need to link tables A and table B with the incident number, to retrieve informations from table B into table A using the RELATED function. I can't make this relation and get the error message below (google translation) :

You cannot create an active relationship between table A and table B, as this might introduce ambiguity between the tables Calendar and table A. To make this relationship active, first deactivate or delete one of the relations between Calendar and table A.

To my idea I need the relations between Calendar and table A to be able to filter datas on both tables.

How can I make this relation ? Or how can I retrieve informations from table B into table A keeping the relations of theses tables with my consolidate tables Calendar and Owner ?

Thanks for you help 🙂

[nvprasad]

Hi Bruno_M,

You can create inactive relationship and while creating measure or calculated column use "USERELATIONSHIP" dax function to turn inactive relationship to active.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[Anonymous]

Hi N V Durga Prasad,

Thanks for your help !

Your solution didn't work in the first time : I had an error message like :

A table of multiple values ​​was provided when a single value was expected.

and I understand now that it is because my incidents table contains duplicates.

Now that I create the bridge proposed by @mahoneypat your solution also work

... and  now I know about the USERELATION function 😅

Best regards

[mahoneypat · Accepted solution]

Two potential ways to address this in your model

1. If the Alerts and Incidents table are similar (columns with same/similar names), you can make the column names match, add a custom column to each with "Incidents" or "Alerts" (so you can use it as a slicer), and then append them in the query editor, and disable load on the original tables/queries.

2. Make a bridge table - either in the query editor (by appending them, keep just the incidentnumber column and removing duplicates) or with DAX (with UNION, DISTINCT, and SELECTCOLUMNS).  You would then have 1:many from the bridge table to each of the incidents and alerts table.

Pat

[Anonymous]

Hi Pat,

Thanks for your help.

I chose solution number 2 with using a bridge table : beginning with Power BI it seem, for me, easier to apply then the first one.

One last question : I found two ways to get my informations from table B (incidents) to into table A (alerts)

The first one is the following :

incident open at =

CALCULATE(

VALUES( 'INCIDENTS'[open]),

USERELATIONSHIP('INCIDENTS'[number], 'BRIDGE_TABLE'[number])

)

The second is :

incident open at =

CALCULATE(

VALUES( 'INCIDENTS'[open]),

FILTER(

'INCIDENTS',

'INCIDENTS'[number] = EARLIER( ALERTS[number])

)
)

Can you tell me what is the best way, and why ?

Many thanks 👍👍👍

I can start my week with positives vibes 😁