Merge rows on single id in Direct Query

Question

Hi everyone,&nbsp;The backgroundI'm trying to build a performance management dashboard for a multi-stage customer complaints process.&nbsp; The data comes from a case management/CRM system and is shared by a third party supplier through direct query.&nbsp; Unfortunately I can't share any of the actual data.&nbsp;The story so farI'm trying to use data from the Case_Audit table to track how long things are in a queue waiting to picked up.&nbsp; I've filtered the data in Power BI by history type 'Case re-assigned' and&nbsp;by contains 'Allocation Queue'.&nbsp;Here is what the data looked like:CaseIdCreatedHistory typeDescription000101/08/24Case re-assignedAssigned from John Smith to Allocation Queue000104/08/24Case re-assignedAssigned from Allocation Queue to Linda Brown000202/08/24Case re-assignedAssigned from Mary O'Brien to Allocation Queue000204/08/24Case re-assignedAssigned from Allocation Queue to John McPhee............&nbsp;What I've done so farI created the following DAX calculated columns:&nbsp;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())&nbsp;And the following DAX measures:&nbsp;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)&nbsp;&nbsp;This is how my table looks at the moment:CaseIdCreatedAllocated date measureRe-allocated measureAllocation days000101/08/2401/08/24&nbsp;&nbsp;000104/08/24&nbsp;04/08/24&nbsp;000202/08/24&nbsp;02/08/24&nbsp;&nbsp;000204/08/24&nbsp;04/08/24&nbsp;...............&nbsp;The problemI 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:CaseIdCreatedAllocated date measureRe-allocated measureAllocation days000101/08/2401/08/2404/08/243000202/08/2402/08/2404/08/242...............&nbsp;Thanks in advance!

lbendlin · Answer

Use a table visual
Add the case id
Add the Created column twice
Set the first to Min and the second to Max

mjds · Answer

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

lbendlin · Answer

How would that look like?

mjds · Answer

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.

Forum Discussion

Merge rows on single id in Direct Query

4 Replies

Related content

merging direct query queries

Merge tables using Direct Query

MERGE QUERIES

Merge two rows into a single row

Async merge risk

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
...	...	...	...

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
...	...	...	...	...