cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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!

example.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Ambiguous Relationships

Hi @megm001,

 

I may misunderstand your requirement previously.Smiley LOL

 

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.

 

rs1.PNG

 

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.

 

r3.PNG

 

Here is sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Ambiguous Relationships

Hi @megm001,




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

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

r2.PNG

 

Regards

View solution in original post

9 REPLIES 9
Highlighted
Microsoft
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. Smiley Happy

 

relationship.PNG

 

Regards

Highlighted
Helper I
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 columnsFirst 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!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.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...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
Helper I

Re: Ambiguous Relationships

@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 columnsFirst 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!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.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...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
Helper I

Re: Ambiguous Relationships

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

Re: Ambiguous Relationships

Hi @megm001,

 

I may misunderstand your requirement previously.Smiley LOL

 

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.

 

rs1.PNG

 

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.

 

r3.PNG

 

Here is sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

Highlighted
Helper I
Helper I

Re: Ambiguous Relationships

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

Highlighted
Helper I
Helper I

Re: Ambiguous Relationships

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

What's the total date?What's the total date?

 

Highlighted
Microsoft
Microsoft

Re: Ambiguous Relationships

Hi @megm001,




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

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

r2.PNG

 

Regards

View solution in original post

Highlighted
Helper I
Helper I

Re: Ambiguous Relationships

Perfect! Thank you! 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

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

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