Filtering DirectQuery table based on import mode table - visuals

Hi Julia1234 ,

When working with a DirectQuery table and attempting to filter it using imported tables, the challenge lies in ensuring consistent results across visuals with different granularities. Since merging is not an option with DirectQuery, here’s an alternative approach using DAX measures and relationships.

Create Filtering Logic with Measures:

Use measures to dynamically exclude rows from the VendorTransaction table based on ExcludeVendor and ExcludeInvoice.

Here’s how:

Measure: Exclude Rows

ExcludeFilter = 
IF (
    NOT (
        VendorTransaction[Vendor] IN VALUES ( ExcludeVendor[Vendor] )
        || VendorTransaction[Invoice] IN VALUES ( ExcludeInvoice[Invoice] )
    ),
    1,
    0
)

Measure: Filtered Amount

FilteredAmt = 
CALCULATE (
    SUM ( VendorTransaction[Amt] ),
    FILTER ( VendorTransaction, [ExcludeFilter] = 1 )fUse FilteredAmt in your visuals instead of Amt. This will ensure that only rows not excluded by ExcludeVendor and ExcludeInvoice are considered, regardless of the visual’s granularity. 

Alternative Approach: Use Composite Models

If your DirectQuery source allows, you can enable a Composite Model. This would let you create relationships between VendorTransaction and the imported tables (ExcludeVendor and ExcludeInvoice) directly in Power BI.

Steps:

1. Ensure relationships between:
   • VendorTransaction[Vendor] and ExcludeVendor[Vendor]
   • VendorTransaction[Invoice] and ExcludeInvoice[Invoice]
2. Use a calculated column in the VendorTransaction table to filter rows:

   IsExcluded = 
   IF (
       VendorTransaction[Vendor] IN VALUES ( ExcludeVendor[Vendor] )
       || VendorTransaction[Invoice] IN VALUES ( ExcludeInvoice[Invoice] ),
       TRUE,
       FALSE
   )

3. Create a measure to sum only non-excluded rows:

   FilteredAmt = 
   CALCULATE (
       SUM ( VendorTransaction[Amt] ),
       VendorTransaction[IsExcluded] = FALSE
   )

You could try

ExcludeVendorExcel =
CALCULATE (
    COUNTROWS ( VendorTransaction ),
    KEEPFILTERS (
        NOT VendorTransaction[Vendor] IN VALUES ( VendorExclusion[Vendor] )
    )
)