Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Removing duplicate rows, data from different (joined) data sources

I have a report that's a combination SalesOrderHeaders and SalesOrderLines:   Customer, Service Purchased, Invoice Date, Amount Due   Boston College, Subscription for HE, 4/5/2020, $400 York Cit...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    ...or I have to build a named TABLE out of at least three database joins, filter it down and then jump over to the report view to finish the summary. Parking it for tonight - shouldn't be doing work today anyway 🙂

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thankyou Allison (yet again) 🙂

 

trying:

 

LatestDate =
IF(
COUNTROWS(FILTER('CustAgedBalances', 'FreeTextInvoiceHeaders'[InvoiceDate] >=EARLIER('FreeTextInvoiceHeaders'[InvoiceDate]) && 'FreeTextInvoiceheaders'[CustomerAccount] = EARLIER('FreeTextInvoiceHeaders'[CustomerAccount]))) = 1, 1, 0)

 

But getting:

 

A single value for column 'InvoiceDate' in table 'FreeTextInvoiceHeaders' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Will look at it once I have some meeting prep out of the way (normal pre-Xmas rush).

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    Anonymous  Yup, that's because [InvoiceDate] is not in the same table that you're filtering. I can't remember (but feel like I know this from another post or even this thread), is CustAgedBalances the fact table/SalesOrderLines? 

     

    If so, you should be able to use RELATED() function I think: 

     

    LatestDate =
    IF(
    COUNTROWS(FILTER('CustAgedBalances', RELATED('FreeTextInvoiceHeaders'[InvoiceDate] ) >=EARLIER(RELATED('FreeTextInvoiceHeaders'[InvoiceDate])) && RELATED('FreeTextInvoiceheaders'[CustomerAccount]) = EARLIER(RELATED('FreeTextInvoiceHeaders'[CustomerAccount])))) = 1, 1, 0)
     
    If that doesn't work, you can add explicit columns as a workaround, but that shouldn't be necessary. 
     
    Please use @ to mention me in your replies, and I'll follow up on them tomorrow (it's very late here in NZ, so am going to sign off for the evening now). 
     
    Merry Christmas!