Help dealing with "duplicate" rows within Summarize

[uk_tj]

Hi Guys,

Could really use some assistance on this one...

I have a table that shows processes assigned to sales and i need be able to report the number of sales per process state per process on a given date.

Here is an example of my data.

CountSales = 
--Date User has selected
--Unrelated
VAR mindt = MIN(DATES[Date])
RETURN
COUNTX(
	--Find process active on date user selects
	SUMMARIZE(
			FILTER(SALES_PROCESS,SALES_PROCESS[validfrom] <= mindt && SALES_PROCESS[validTo] >= mindt),
			SALES_PROCESS[SaleID],SALES_PROCESS[Commission]
		),
	--Count the number of sales
	SALES_PROCESS[SaleID]
)

This is what I'm using but it double counts when there are multiple processes active on the selected date.

When there are multiple processes on the selected date I need to

1) Count the one with the lowest process state number. [See Sale 3 & 5]

2) If the process state numbers are the same I need to count the first one [See Sale 1] 

Any help would be very much appreciated.

[uk_tj]

This is where I'm at atm...

Needless to say this is not returning the correct results.

CountSales = 
--Date User has selected
--Unrelated
VAR mindt = MIN(DATES[Date])													
RETURN
COUNTROWS(
	--Filter summary to show only processes where the index equals the minimum valid index for that sale
	FILTER(
		ADDCOLUMNS(
			ADDCOLUMNS(
				SUMMARIZE(
					--Filter table1 to show ALL valid processes then summarise
					FILTER(ALL(Table1),Table1[validfrom] <= mindt && Table1[validTo] >= mindt),
					Table1[SaleID],Table1[Index], Table1[SaleProcess],Table1[ProcessState]
					),
			"MinState",
			--For each row in summary find the minimum process state for that sale
			CALCULATE(
				MIN(Table1[ProcessState]),
				FILTER(Table1,Table1[SaleID]=[SaleID]&&Table1[validfrom]<=mindt&&Table1[validTo]>=mindt)
			)),
		"MinIndex",
		--For each row in summary find minimum index for that sale
		CALCULATE(
			MIN(Table1[Index]),
			FILTER(Table1,Table1[SaleID]=[SaleID]&&Table1[ProcessState]=[MinState]&&Table1[validfrom]<=mindt&&Table1[validTo]>=mindt)
		      )
			),
	[Index]=[MinIndex]
	)
)

[richbenmintz]

haven't given up, made some progess this morning then once again my job got in the way. will look again tonight

[uk_tj]

@richbenmintz thats great as the only progression on mind side is the severity of my headache 🙂

[richbenmintz · Accepted solution]

hi @uk_tj,

I think I have cracked it at least against the sample data set.

created 

1 testing measure and 2 filtering measures

//Find rows in date range
in range = CALCULATE(COUNTROWS('sample'), FILTER('sample','sample'[validfrom] <= min(DATES[Date]) && 'sample'[validTo] >= MIN('DATES'[Date])))

//set rows to blank() if not in range
//Find min datefrom per salesId
MinDatePerGroup = if([in range] = 1, CALCULATE(min('sample'[ValidFrom]), filter(ALLEXCEPT('sample', 'sample'[SaleID]), [in range] = 1)), blank())

//set rows to blank() if not in range

//find min ProcessState per salesId minProcessStatePerGroup = if([in range] = 1, CALCULATE(min('sample'[ProcessState]), filter(ALLEXCEPT('sample', 'sample'[SaleID]), [in range] = 1)), blank())

with these mesure created I can count the rows where the date = the min date and the processState = the min ProcessState

Count = CALCULATE(COUNTROWS('sample'), filter('sample', [ValidFrom]=[MinDatePerGroup] && [ProcessState] = [minProcessStatePerGroup]))

If there are two with the same state are the same then the min date wins, if the process states are different but the date is the same then the min process state wins.

link to the pbix below

pbix download

[uk_tj]

Thanks @richbenmintz that did the trick! Was kind of like what I was trying to achieve within a summarise structure but your method is no doubt more efficient with the added bonus of actually working 🙂

Your help is very much appreciated.

[richbenmintz]

Hi @uk_tj,

Honestly I started off with a summerize approach as well, however it became apparent that trying to manage the context and apply blank() to the rows that needed to be hidden was going to make me crazy. went back to the tried and true approach of turning the problem into a series of filtering steps and voila a solution that worked and hopefully will continue to work

[uk_tj]

Bump

[richbenmintz]

sorry, got sucked into work on Friday. I will try to get to it tonight or tomorrow

[uk_tj]

bump

[richbenmintz]

Hi @uk_tj,

You could try using the topn to your function to return a single row per grouping. If you are able to send some sample data or pbix. it would save me from having to transcribe it from your screenshot.

Thanks,

[uk_tj]

Thanks for your reply @richbenmintz, I was beginning to think I smell or something 🙂

Here is a link to My File

Let me know if you have any questions

[richbenmintz]

Hi @uk_tj,

Could you send me the data from your example screenshot, would be easier to work with that.

Sorry.

[uk_tj]

Hi @richbenmintz,

No worries, here you go... example data

Cheers

[uk_tj]

Any ideas? or am I boned?