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

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

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

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

        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