Forum Discussion

megm001's avatar
megm001
Icon for Advocate II rankAdvocate II
9 years ago
Solved

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 relati...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

megm001's avatar
megm001
Icon for Advocate II rankAdvocate II
9 years ago

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

What's the total date?

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

    • megm001's avatar
      megm001
      Icon for Advocate II rankAdvocate II
      9 years ago

      Perfect! Thank you!