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.     In the Table: Call for Technical Assi...
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.

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

Hi,

 

I do not understand your end result.  For BR2, May 5 and May 7 are only 2 days apart.  So why should there be a repair date for May 7?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Ashish_Mathur,

Thank you for your interest in helping me.

 

In my example,

BR2 had 2 Call_Date in less than 30 days, so I need to ignore the first date in less than 30 days (May 5).

 

The same applies for BR0, that has 3 Call_Dates in less than 30 days.

I need to ignore the first and second date (July 6 and 8), because the last date (July 10) is closest to the date of Repair_Date.

 

 

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

    Hi,

     

    The example that you have shown in the picture is different from the one you shared initially.  Paste the two datasets here where in a format that i can paste them in Excel.  Ensure that the dataset that you share now has the duplicate problem that you have shown in your latest post.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      Yes, I added more lines in the last example.

       

      The two table below has the duplicate problem that I shown in my latest post.

       

       Table Call for Technical Assistance

      Call_DateSerial_Number
      2018-01-01BR1
      2018-01-02BR2
      2018-01-03BR4
      2018-01-04BR0
      2018-03-05BR1
      2018-05-05BR2
      2018-07-06BR0
      2018-07-08BR0
      2018-07-10BR0

       

       Technical Repair

      Repair_DateSerial
      2018-02-05BR1
      2018-02-10BR2
      2018-02-10BR0
      2018-02-11BR4
      2018-04-01BR1
      2018-07-01BR2
      2018-08-01BR0
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        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.