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

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 :)

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