Select values based on distinct columns and ordering by date

[Anonymous]

I need help creating a DAX command:

Sample Data:

Cover	Workcase	UserId	ProvidersCount	Workcase Update Date	Cover Update Date
12345	9875	xxx123	7	1/22/2020
12345	9875	xxx123	10	1/20/2020
12345	9875	xxx456	11	1/25/2020
67890		xxx123	10		2/20/2020
67890	1234	xxx123	6	2/25/2020

I want a measure to calculate ProvidersCount for each unique cover, workcase, userID combination. But it needs to meet this criteria:

- If cover, workcase, userID combination happens more than once, select the ProvidersCount value from the record with the most recent workcase update date. If the workcase update date is empty, then use the most recent cover update date. Then sum the providerscount values that were selected.

So if I put it into a table I want to see:

UserID          ProvidersCount

xxx123          23 (23 comes from 7 + 10 + 6)

xxx456          11

Thanks!

[Anonymous]

Hi @Anonymous ,

There must be a more elegant (daxier) solution, but this is my two cents: 

For me this measure show the desired result: 

CPC = 
var v_UserId = min(covers[UserID])
return
row("x",
SUMX(
ADDCOLUMNS(
groupby ( 
'Covers', 
Covers[Cover], 
Covers[Workcase], 
Covers[UserID]
),
"yy", 
var v1 = Covers[Cover]
var v2 = Covers[Workcase]
var v3 = covers[UserID]
var v4 = CALCULATE(max(Covers[WorkcaseUpdateDate]))
var v5 = CALCULATE(max(Covers[CoverUpdateDate]))
RETURN CALCULATE(
sum(Covers[ProvidersCount]), 
Covers, 
Covers[Cover]=v1, 
Covers[Workcase] = v2, 
Covers[UserID]= v3,
Filter(Covers,
(NOT ISBLANK(v4) && Covers[WorkcaseUpdateDate] = v4) ||
(ISBLANK(v4) && Covers[CoverUpdateDate] = v5)
))
),
if (Covers[UserID] = v_UserId, [yy],0))
)

 

Hope this helps.

Jan 

if this is a solution for you, don't forget to mark it as such. thanks

[Anonymous]

Hi Jan,

This did help. However, I also need to do this for a different set of criteria as well. Instead of Cover, Workcase, UserID combinations, I need to do it for Cover, Workcase, Workgroup (another field - several users can be in one workgroup and if multiple users in the same workgroup have worked on the same cover/workcase combo the providerscount should only be counted at the most recent date). I created the same formula for workgroup and it seems like it would work but it takes a very long time to load in the table. In fact, it typically times out and breaks the table.

I was wondering if there would be a way to change this formula to just pair up unique Cover/Workcase combinations so that when I place it into a table broken down at UserID level it will compute using UserID and when I place it into a table broken down at workgroup level it will compute using Workgroup. 

I tried just removing everything related to UserID, but SUMX expects an expression at the end, and I'm not sure what that expression would be for it to be dynamic like that. I am also not sure if it will speed up the processing time by making this change, but I thought it would be worth a shot. 

[Anonymous]

[Provider Count] =
var __infoData =
	ADDCOLUMNS(
		SUMMARIZE(
			Data,
			Data[Cover],
			Data[Workcase],
			Data[UserId]
		),
		"RowCount",
			CALCULATE( COUNTROWS( Data ) ),
		"LatestWorkcaseUpdateDate",
			CALCULATE( MAX( Data[Workcase Update Date] ) ),
		"LatestWorkcaseUpdateDateIsBlank",
			ISBLANK(
				CALCULATE( MAX( Data[Workcase Update Date] ) )
			),
		"LatestCoverUpdateDate",
			CALCULATE( MAX( Data[Cover Update Date] ) )
	)
var __resultForRowCountEqual1 =
	CALCULATE(
		SUM( Data[ProvidersCount] ),
		filter(
			__infoData,
			[RowCount] = 1
		)
	)
var __resultForRowCountMoreThan1AndWorkcaseUpdateDate =
	CALCULATE(
		SUM( Data[ProvidersCount] ),
		TREATAS(
			SELECTCOLUMNS(
				filter(
					__infoData,
					[RowCount] > 1
					&& not( [LatestWorkcaseUpdateDateIsBlank] )
				),
				"A", Data[Cover],
				"B", Data[Workcase],
				"C", Data[UserId],
				"D", [LatestWorkcaseUpdateDate]
			),
			Data[Cover],
			Data[Workcase],
			Data[UserId],
			Data[Workcase Update Date]
		)
	)
var __resultForRowCountMoreThan1AndNoWorkcaseUpdateDate =
	CALCULATE(
		SUM( Data[ProvidersCount] ),
		TREATAS(
			SELECTCOLUMNS(
				filter(
					__infoData,
					[RowCount] > 1
					&& [LatestWorkcaseUpdateDateIsBlank]
				),
				"A", Data[Cover],
				"B", Data[Workcase],
				"C", Data[UserId],
				"D", [LatestCoverUpdateDate]
			),
			Data[Cover],
			Data[Workcase],
			Data[UserId],
			Data[Cover Update Date]
		)
	)
var __result =
	__resultForRowCountEqual1
		+ __resultForRowCountMoreThan1AndWorkcaseUpdateDate
		+ __resultForRowCountMoreThan1AndNoWorkcaseUpdateDate
RETURN
	__result

Will the above be better in terms of speed?

Best

D