Merge rows on single id in Direct Query

MJDS — Fri, 09 Aug 2024 14:19:35 GMT

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!

Re: Merge rows on single id in Direct Query

lbendlin — Fri, 09 Aug 2024 17:12:02 GMT

Use a table visual

Add the case id

Add the Created column twice

Set the first to Min and the second to Max

Re: Merge rows on single id in Direct Query

MJDS — Mon, 12 Aug 2024 09:38:28 GMT

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

Re: Merge rows on single id in Direct Query

lbendlin — Tue, 13 Aug 2024 00:55:38 GMT

How would that look like?

Re: Merge rows on single id in Direct Query

MJDS — Tue, 13 Aug 2024 08:10:54 GMT

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.

topic Re: Merge rows on single id in Direct Query in DAX Commands and Tips

Merge rows on single id in Direct Query

Re: Merge rows on single id in Direct Query

Re: Merge rows on single id in Direct Query

Re: Merge rows on single id in Direct Query

Re: Merge rows on single id in Direct Query