Ambiguous Relationships

[megm001]

I have customers buying oranges from one company and I am surveying them about the taste on a monthly basis. If a company has a low mean rating in a given month, we contact them. My tables and relationships are shown in orange.

I want to create a table to show each company's monthly  mean rating and a contact date, if available. My issue is that the contact date needs to be filtered by both company and month, but one relationship is active, while the other is inactive. Hence the filtering issue.

Any help would be greatly appreciated. Thanks!

[v-ljerr-msft · Accepted solution]

Hi @megm001,

I may misunderstand your requirement previously.

In your scenario, you can just remove the "Month Sort" table from your mode, then create bidirectional relationships among the other three tables like below.

And then you should be able to use the formula below to create a measure to get the Contact Date and show it with other three columns.

Measure for Contact Date = 
CALCULATE (
    MAX ( Table4[Contact Date] ),
    FILTER ( Table4, Table4[Month of Low Rating] = MAX ( Table1[MonthSurveyed] ) )
)

Note: replace Table1 and Table4 with your real table name, and format the measure to show only Month and Day under Modeling tab.

Here is sample pbix file for your reference. 

Regards

[v-ljerr-msft]

Hi @megm001,

Based on my test, if you set Cross filter direction to Single for all relationships, then you should be able to make all relationships active and then filter by both company and month in your scenario. 

Regards

[megm001]

@v-ljerr-msftThank you for your response!

I was able to create the first three columns of my table with the relationships you specified.

However, when I added the "fourth" column (Contact Date splits into to Month, Day, Year, Quarter. I only need Month and Day, as shown below.), the filtering fails and each month of each company shows every day of the year. That's 730 entries!

The filter broke! 

So I tried creating a bidirectional relationship between Companies[Company] and Performance Review [Company], which resulted in the following table. You can see that it's much better than the previous, but something's off. It may be filtering the company correctly (It's identified that there are two contact dates for Company a and only one for Company B. However, it's showing both contact dates for January! That's not right! If we look at the Performance Review table again, we see that each date refers to a review in Jan and Feb, respectively.

Since we can't filter bidirectionally for both company and month (Month Sort[Month Int] and Performance Review[Month of Low Rating]) I tried filtering bidirectionally for just month. However, we get a similar result to filtering bidirectionally for company only. As seen below. Again, it sees the one contact date in January and two in February, but it's not connecting it to the companies.

Again, not quite... Now the companies are off. Grr...

Is it possible to filter bidirectionally for both company and month? Is there a calculation I can use to fix this? Maybe using userelationship()?

Thank you!

[v-ljerr-msft · Accepted solution]

Hi @megm001,

I may misunderstand your requirement previously.

In your scenario, you can just remove the "Month Sort" table from your mode, then create bidirectional relationships among the other three tables like below.

And then you should be able to use the formula below to create a measure to get the Contact Date and show it with other three columns.

Measure for Contact Date = 
CALCULATE (
    MAX ( Table4[Contact Date] ),
    FILTER ( Table4, Table4[Month of Low Rating] = MAX ( Table1[MonthSurveyed] ) )
)

Note: replace Table1 and Table4 with your real table name, and format the measure to show only Month and Day under Modeling tab.

Here is sample pbix file for your reference. 

Regards

[megm001]

@v-ljerr-msft Is there a way to not show the "total" date. 

What's the total date?

[v-ljerr-msft · Accepted solution]

Hi @megm001,

---

Based on my test, the formula below should work to not show the "total" date. 

Measure for Contact Date = 
IF (
    HASONEVALUE ( Table1[Company] ),
    CALCULATE (
        MAX ( Table4[Contact Date] ),
        FILTER ( Table4, Table4[Month of Low Rating] = MAX ( Table1[MonthSurveyed] ) )
    )
)

Regards

[megm001]

Perfect! Thank you! 

[megm001]

@v-ljerr-msftThank you! It works like a charm!

[megm001]

@v-ljerr-msftThank you for your response!

I was able to create the first three columns of my table with the relationships you specified.First three columns

However, when I added the "fourth" column (Contact Date splits into to Month, Day, Year, Quarter. I only need Month and Day, as shown below.), the filtering fails and each month of each company shows every day of the year. That's 730 entries!

The filter broke!

So I tried creating a bidirectional relationship between Companies[Company] and Performance Review [Company], which resulted in the following table. You can see that it's much better than the previous, but something's off. It may be filtering the company correctly (It's identified that there are two contact dates for Company a and only one for Company B. However, it's showing both contact dates for January! That's not right! If we look at the Performance Review table again, we see that each date refers to a review in Jan and Feb, respectively.

Almost there, but not quite... The month's a bit off. 

Since we can't filter bidirectionally for both company and month (Month Sort[Month Int] and Performance Review[Month of Low Rating]) I tried filtering bidirectionally for just month. However, we get a similar result to filtering bidirectionally for company only. As seen below. Again, it sees the one contact date in January and two in February, but it's not connecting it to the companies.

Again, not quite... Now the companies are off. Grr...

Is it possible to filter bidirectionally for both company and month? Is there a calculation I can use to fix this? Maybe using userelationship()?

Thank you!

[megm001]

@v-ljerr-msft Thank you for your response!

I was able to create the first three columns of my table with the relationships you specified.First three columns

However, when I added the "fourth" column (Contact Date splits into to Month, Day, Year, Quarter. I only need Month and Day, as shown below.), the filtering fails and each month of each company shows every day of the year. That's 730 entries!

The filter broke!

So I tried creating a bidirectional relationship between Companies[Company] and Performance Review [Company], which resulted in the following table. You can see that it's much better than the previous, but something's off. It may be filtering the company correctly (It's identified that there are two contact dates for Company a and only one for Company B. However, it's showing both contact dates for January! That's not right! If we look at the Performance Review table again, we see that each date refers to a review in Jan and Feb, respectively.

Almost there, but not quite... The month's a bit off.

Since we can't filter bidirectionally for both company and month (Month Sort[Month Int] and Performance Review[Month of Low Rating]) I tried filtering bidirectionally for just month. However, we get a similar result to filtering bidirectionally for company only. As seen below. Again, it sees the one contact date in January and two in February, but it's not connecting it to the companies. 

Again, not quite... Now the companies are off. Grr...

Is it possible to filter bidirectionally for both company and month? Is there a calculation I can use to fix this? Maybe using userelationship()?

Thank you!