cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Ambiguous Relationships

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft

## Re: Ambiguous Relationships

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

Highlighted
Microsoft

## Re: Ambiguous Relationships

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

9 REPLIES 9
Highlighted
Microsoft

## Re: Ambiguous Relationships

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

Highlighted
Helper I

## Re: Ambiguous Relationships

@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!

Highlighted
Helper I

## Re: Ambiguous Relationships

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!

Highlighted
Helper I

## Re: Ambiguous Relationships

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!

Highlighted
Microsoft

## Re: Ambiguous Relationships

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

Highlighted
Helper I

## Re: Ambiguous Relationships

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

Highlighted
Helper I

## Re: Ambiguous Relationships

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

What's the total date?

Highlighted
Microsoft

## Re: Ambiguous Relationships

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

Highlighted
Helper I

## Re: Ambiguous Relationships

Perfect! Thank you!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors