Calculate customers who are in contact with different departments

[cale23]

I have a table called "cases" with the following columns and rows:


I need to calculate how many times a customer have been in contact with department B in less than one hour after being in contact with department A. In the dataset above, only customerID 1 and 6  are the correct answers. Can anyone help me with this calculation?

Additional info: in the real dataset I have more than 4 million rows with cases.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[cale23]

Thanks for the reply. It looks I don't have enough memory to iterate through every row for the calculated columns unfortunately 😞

[danextian · Accepted solution]

With 4 million rows, iterating through a table is out of questions.

In the query editor, reference/duplicate your original table and filter it to be only. Load both tables and a lookupvalue from the complete table to table B. This should be a lot faster than iterating through that much rows.

[danextian]

Hi @cale23 ,

Please post a workable sample data and not an image.