Many to Many and Circular Dependency

[Anonymous]

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])
    )
)

[Ashish_Mathur · Accepted solution]

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 · Accepted solution]

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]

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:

[Ashish_Mathur]

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]

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]

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]

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_Date	Serial_Number
2018-01-01	BR1
2018-01-02	BR2
2018-01-03	BR4
2018-01-04	BR0
2018-03-05	BR1
2018-05-05	BR2
2018-07-06	BR0
2018-07-08	BR0
2018-07-10	BR0

 Technical Repair

Repair_Date	Serial
2018-02-05	BR1
2018-02-10	BR2
2018-02-10	BR0
2018-02-11	BR4
2018-04-01	BR1
2018-07-01	BR2
2018-08-01	BR0

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

@Ashish_Mathur You are awesome!

This formula that's exactly what I needed.

Thanks for your patience and support.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

You are welcome.