Merge rows on single id in Direct Query

[MJDS]

Hi everyone,

The background

I'm trying to build a performance management dashboard for a multi-stage customer complaints process.  The data comes from a case management/CRM system and is shared by a third party supplier through direct query.  Unfortunately I can't share any of the actual data.

The story so far
I'm trying to use data from the Case_Audit table to track how long things are in a queue waiting to picked up.  I've filtered the data in Power BI by history type 'Case re-assigned' and by contains 'Allocation Queue'.

Here is what the data looked like:

CaseId	Created	History type	Description
0001	01/08/24	Case re-assigned	Assigned from John Smith to Allocation Queue
0001	04/08/24	Case re-assigned	Assigned from Allocation Queue to Linda Brown
0002	02/08/24	Case re-assigned	Assigned from Mary O'Brien to Allocation Queue
0002	04/08/24	Case re-assigned	Assigned from Allocation Queue to John McPhee
...	...	...	...

What I've done so far

I created the following DAX calculated columns:

Allocation direction = IF(CONTAINSSTRING('Case_Audit'[Description],"to Allocation Queue"),"To","From")

Allocated date = IF(Case_Audit[Allocation direction]="To",'Case_Audit'[Created],BLANK())

Re-allocated date = IF(Case_Audit[Allocation direction]="From",'Case_Audit'[Created],BLANK())

And the following DAX measures:

Allocated date measure = MAX('Case_Audit'[Allocated date])

Re-allocated measure = MAX('Case_Audit'[Re-allocated date])

Allocation days = DATEDIFF('Case_Audit'[Allocated date measure],'Case_Audit'[Re-allocated measure],DAY)

 This is how my table looks at the moment:

CaseId	Created	Allocated date measure	Re-allocated measure	Allocation days
0001	01/08/24	01/08/24
0001	04/08/24		04/08/24
0002	02/08/24	02/08/24
0002	04/08/24		04/08/24
...	...	...	...	...

The problem

I need to find a method that works in Direct Query and doesn't use Power Query to collapse the data so that it looks like this:

CaseId	Created	Allocated date measure	Re-allocated measure	Allocation days
0001	01/08/24	01/08/24	04/08/24	3
0002	02/08/24	02/08/24	04/08/24	2
...	...	...	...	...

Thanks in advance!

[lbendlin]

Use a table visual

Add the case id

Add the Created column twice

Set the first to Min and the second to Max

[MJDS]

Thanks, is there a way I could also use this method in a clustered column visual?

[lbendlin]

How would that look like?

[MJDS]

I was thinking an average of the allocation days in the y axis and then month from a date table linked to the created value in the model.