Forum Discussion
Ambiguous Relationships
Hi megm001,
I may misunderstand your requirement previously.:smileylol:
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. :smileyhappy:
Regards
Hi megm001,
Based on my test, the formula below should work to not show the "total" date. :smileyhappy:
Measure for Contact Date = IF ( HASONEVALUE ( Table1[Company] ), CALCULATE ( MAX ( Table4[Contact Date] ), FILTER ( Table4, Table4[Month of Low Rating] = MAX ( Table1[MonthSurveyed] ) ) ) )Regards
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. :smileyhappy:
Regards
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!
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!
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!
Hi megm001,
I may misunderstand your requirement previously.:smileylol:
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. :smileyhappy:
Regards
v-ljerr-msftThank you! It works like a charm!
