Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Many to Many and Circular Dependency

Hi Folks,

 

Could anyone help a newbie on powerBI?

I have two table, the first is Call for Technical Assistance and second is Technical Repair.

 

Table: Call for Technical Assistance Table: Technical Repair

In the Table: Call for Technical Assistance, I want to add a Column with the First Repair Date after the Call Date.

Like this:

 

 

 

I tried following DAX, but there is some problem with Circular Dependency:

Repair_Date = CALCULATE(
    LOOKUPVALUE(
            'Technical Repair'[Repair_Date]; 
            'Technical Repair'[Serial_Number]; MAX([Serial_Number]);
            'Technical Repair'[Repair_Date]; FIRSTDATE('Technical Repair'[Repair_Date])
    )
)
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    This calculated column formula in Table1 works fine

     

    =CALCULATE(MIN(Table2[Repair_Date]),FILTER(Table2,Table2[Serial_Number]=EARLIER(Table1[Serial_Number])&&Table2[Repair_Date]>EARLIER(Table1[Call_Date])))

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Try this calculated column formula

     

    =if(ISBLANK(COUNTROWS(FILTER(Table1,Table1[Serial_Number]=EARLIER(Table1[Serial_Number])&&Table1[Call_Date]>EARLIER(Table1[Call_Date])))),CALCULATE(MIN(Table2[Repair_Date]),FILTER(Table2,Table2[Serial]=EARLIER(Table1[Serial_Number])&&Table2[Repair_Date]>EARLIER(Table1[Call_Date]))),if(CALCULATE(MIN(Table1[Call_Date]),FILTER(Table1,Table1[Serial_Number]=EARLIER(Table1[Serial_Number])&&Table1[Call_Date]>EARLIER(Table1[Call_Date])))-[Call_Date]<=30,BLANK(),CALCULATE(MIN(Table2[Repair_Date]),FILTER(Table2,Table2[Serial]=EARLIER(Table1[Serial_Number])&&Table2[Repair_Date]>EARLIER(Table1[Call_Date])))))

     

    Hope this helps.

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    This calculated column formula in Table1 works fine

     

    =CALCULATE(MIN(Table2[Repair_Date]),FILTER(Table2,Table2[Serial_Number]=EARLIER(Table1[Serial_Number])&&Table2[Repair_Date]>EARLIER(Table1[Call_Date])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Ashish_Mathur, Thanks a Lot!
      Your calculated column formula works.

       

      Taking the opportunity.

      In your formula, Is it possible I can insert a conditional, to not return duplicates [Repair_Date] where the last [Call_Date] is smaller than 30 days?

      Like the Example below: