Filtering DirectQuery table based on import mode table - visuals

I have DirectQuery table [VendorTransaction](Vendor,Invoice,Amt) and 2 import tables: ExcludeVendor(Vendor) and ExcludeInvoice(Invoice).
I would need to exclude records in DirectQuery table [VendorTransaction](Vendor,Invoice,Amt) that exist as Vendor in ExcludeVendor and Invoice in ExcludeInvoice. I tried to use this measure, it worked on Visual with granularity similar to [VendorTransaction](Vendor,Invoice,Amt) but return different result (Amt) on Visuals with different granularity (Vendor or Total Amount). Are there any other ways to filter/exclude? I cannot use merge as as [VendorTransaction] is Direct Query table.

ExcludeVendorExcel = COUNTROWS(

FILTER(

VendorTransaction,

NOT VendorTransaction[Vendor] IN VALUES(VendorExclusion[Vendor])

))

Thank you!

johnt75

1 year ago

ExcludeVendorExcel = 
CALCULATE (
    SUM ( VendorTransactionMeasure[Amount] ),
    KEEPFILTERS (
        NOT VendorTransaction[Vendor] IN VALUES ( ExcludeVendor[Vendor] )
        && NOT VendorTransaction[Invoice] IN VALUES ( ExcludeInvoice[Invoice] )
    )
)

seems to work.

Exclude Test.pbix24 KB

Forum Discussion

Filtering DirectQuery table based on import mode table - visuals