Forum Discussion
Helper IFiltering DirectQuery table based on import mode table - visuals
ExcludeVendorExcel = CALCULATE ( SUM ( VendorTransactionMeasure[Amount] ), KEEPFILTERS ( NOT VendorTransaction[Vendor] IN VALUES ( ExcludeVendor[Vendor] ) && NOT VendorTransaction[Invoice] IN VALUES ( ExcludeInvoice[Invoice] ) ) )seems to work.
Helper IHi FarhanJeelani Thank you for your ideas!
I tried creating this measure, but got error: A single value for column 'Vendor' in table 'VendorTransaction' 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.
Should an aggregate function be added?
ExcludeFilter =
IF (
NOT (
VendorTransaction[Vendor] IN VALUES ( ExcludeVendor[Vendor] )
|| VendorTransaction[Invoice] IN VALUES ( ExcludeInvoice[Invoice] )
),
1,
0
)
Super UserHi,
Please share some sample data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
- Julia1234
Hi Ashish_Mathur Thank you for responding,
Direct Tables (not db tables but from model.bim) . VendorTransaction related to VendorTransactionMeasure by ID.
Excpected result: 1000 = 400+ 600
IDs 1,2,3,5 excluded, IDs 4 and 6 included into Amount calculation
1.Direct TablesVendorTransaction
Vendor Invoice ID V123 INV456 1 V124 INV457 2 V125 INV458 3 V125 INV459 4 V127 INV460 5 V127 INV461 6 VendorTransactionMeasure
ID Amount 1 100 2 200 3 300 4 400 5 500 6 600 2.Import Tables
ExcludeVendorVendor V123 V124 ExcludeInvoice
Invoice INV458 INV460 So basically, the report uses existing tabular model.bim with direct connection, and exclusion list is needed on top to exclude Vendors and Invoices from VendorTransactions and VendorTransactions.
Thank you!
- Ashish_Mathur
Hi,
Are you allowed to create calculated column formulas?
- Julia1234
No, as the report is based on tabular model tables with direct query and I am trying to put exclusion list (import mode) on top. It is not possible to create calculated column based on a columns from the model.