• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying same information when use measure

W

windytech

New Member
I got a table named contact. The contact table contains all information about individuals. There is another table named Contact_associate. Contact-associate contains information about the emergency contact of the contact person.

80993

The above picture shows information present in the contact-associate table. AssociatedContactId is the id that is the emergency contact person for Contact ID. The relationship between the two tables is shown as below:
80994

The active realtionship between two tables is through contactId. An inactive relationship exists between AssociatedContactID of contact-associate table and with ContactId of the contact table. I want to display the information about the contact and their emergency contact person in the table visual. As contactID of contact-associate table is directly connected to contact tablem we can drag forename of contactId from contact Table. However, finding name of AssociatedContactId (i.e. emergency contact person) is difficult. So I created a measure which is as follow:

Associated Contact Forename = CALCULATE(max(Contact[Forename]),USERELATIONSHIP(ContactAssociate_1[AssociatedContactId],Contact[ContactId]))

When I display the information in table visual as shown below:

80995

The problem in the visual is that it shows same forename for ContactId and AssociatedContactId. Could anyone help me where am I making the mistake?

Sample here
 
There's no many to many relationship exists. It's one to many. Moreover model is perfectly star schema. The problem is not because of model, it's because of measure
 
Oh I see. I misread your image. It's bi-directional relationship. Why bi-directional? Should be one way relationship no?

Going to one side to many side.

EDIT: Note that simplest way to deal with your issue is to duplicate your contact table and associate one to ContactID and the other to Associated ContactId. Since data model only allows for single active relationship between two tables. This avoids the issue all together.
 
Last edited:
Oh by the way. Issue isn't with your measure. But how you constructed your visiual.

"Forename" in your visual is actually column from Contact. Not a measure. So based on other fields in the visual. It's filtered to the context that matches (i.e. Associated Contact Forename). You need separate measure to calculate this value. Or like my previous suggestion, go with 2 dimension tables and you don't need measure at all.

NOTE: Having column from fact table in your visual is going to complicate issue here as well. Typically, dimension table's columns are added to visual as field. But columns from fact tables are calculated via measure.
 
You must log in or register to reply here.
Back
Top