Forum Discussion
River
Helper IV
4 years agoFilter inside treatas, is it possible
Hi Friends, It's always a struggle with DAX:-) I have a dropdown visual with some hard code text as filter. it will filter out an invoice table first, then, I am going to use 'TreatAs' to joi...
- 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.