Forum Discussion
Filter inside treatas, is it possible
- 4 years ago
River is it meeting your expectation?
_newMeasure = CALCULATE ( - SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ), TREATAS ( SUMMARIZE ( FILTER ( 'Invoice', CONTAINSSTRING ( Invoice[INVOICE], SELECTEDVALUE ( InvoiceFilter[Filter] ) ) ), 'Invoice'[AccountNum], 'Invoice'[Voucher], 'Invoice'[DataAreaId], 'Invoice'[Partition] ), 'Invoice Paid'[AccountNum], 'Invoice Paid'[LastSettleVoucher], 'Invoice Paid'[DataAreaId], 'Invoice Paid'[Partition] ) )Recon and let us know.
- 4 years ago
Try:
Amount Paid FILTER = VAR InvTable = SUMMARIZE ( FILTER ( Invoice, LEFT ( Invoice[INVOICE], 5 ) IN VALUES ( InvoiceFilter[Filter] ) ), 'Invoice'[AccountNum], 'Invoice'[Voucher], 'Invoice'[DataAreaId], 'Invoice'[Partition] ) RETURN CALCULATE ( - SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ), TREATAS ( InvTable, 'Invoice Paid'[AccountNum], 'Invoice Paid'[LastSettleVoucher], 'Invoice Paid'[DataAreaId], 'Invoice Paid'[Partition] ) ) - 4 years ago
Sorry, I'm not sure what you mean. The measure returns the sum for the matching rows from both tables defined in TREATAS. There are invoices which haven't been paid, so they will show up if you include the invoice number in the table, but the measure returns blank of course.
You can try filtering the table using the following as a filter in the filter pane and setting the value to 1:
Filter Table = COUNTROWS ( FILTER ( Invoice, LEFT ( Invoice[INVOICE], 5 ) IN VALUES ( InvoiceFilter[Filter] ) ) )Which returns the following:
For account "5", for example, there are no rows in the paid table matching the criteria established in the TREATAS function.
I've attached the file for your reference
- 4 years ago
It worked, Paul and you basically come to similar approach.
Guys, really appreciate it.
Try:
Amount Paid FILTER =
VAR InvTable =
SUMMARIZE (
FILTER (
Invoice,
LEFT ( Invoice[INVOICE], 5 ) IN VALUES ( InvoiceFilter[Filter] )
),
'Invoice'[AccountNum],
'Invoice'[Voucher],
'Invoice'[DataAreaId],
'Invoice'[Partition]
)
RETURN
CALCULATE (
- SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ),
TREATAS (
InvTable,
'Invoice Paid'[AccountNum],
'Invoice Paid'[LastSettleVoucher],
'Invoice Paid'[DataAreaId],
'Invoice Paid'[Partition]
)
)
- River4 years ago
Helper IV
Hi Paul,
If I add 'invoice' to grid, you will see it's not filtering the invoice. Like screen shot I showed in other posts.
But greatly appreciate your help.
- PaulDBrown4 years ago
Community Champion
Sorry, I'm not sure what you mean. The measure returns the sum for the matching rows from both tables defined in TREATAS. There are invoices which haven't been paid, so they will show up if you include the invoice number in the table, but the measure returns blank of course.
You can try filtering the table using the following as a filter in the filter pane and setting the value to 1:
Filter Table = COUNTROWS ( FILTER ( Invoice, LEFT ( Invoice[INVOICE], 5 ) IN VALUES ( InvoiceFilter[Filter] ) ) )Which returns the following:
For account "5", for example, there are no rows in the paid table matching the criteria established in the TREATAS function.
I've attached the file for your reference
- River4 years ago
Helper IV
Hi Paul,
This file filters out correctly, will try in real solution.
Many thanks.