Forum Discussion

uk_tj's avatar
uk_tj
Icon for Advocate IV rankAdvocate IV
9 years ago
Solved

Help dealing with "duplicate" rows within Summarize

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.

 

 

Need Help
  • richbenmintz's avatar
    richbenmintz
    9 years ago

    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

14 Replies

    • richbenmintz's avatar
      richbenmintz
      Icon for Resident Rockstar rankResident Rockstar
      9 years ago

      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's avatar
        uk_tj
        Icon for Advocate IV rankAdvocate IV
        9 years ago

        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's avatar
      richbenmintz
      Icon for Resident Rockstar rankResident Rockstar
      9 years ago

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

  • uk_tj's avatar
    uk_tj
    Icon for Advocate IV rankAdvocate IV
    9 years ago

    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's avatar
      richbenmintz
      Icon for Resident Rockstar rankResident Rockstar
      9 years ago

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

      • uk_tj's avatar
        uk_tj
        Icon for Advocate IV rankAdvocate IV
        9 years ago

        richbenmintz thats great as the only progression on mind side is the severity of my headache :)