Filtering DirectQuery table based on import mode table - visuals

[Julia1234]

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]

You could try

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

[Julia1234]

Thank you @johnt75 , I tried this logic on visuals with different granularity  and, unfortunatelly,  this filter  returned different result for Amount.

[johnt75 · Accepted solution]

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

seems to work.

[Julia1234]

Thank you @johnt75 for the details, this measure works.

[FarhanJeelani]

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
   )

[Julia1234]

Hi @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
)

[Ashish_Mathur]

Hi,

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 Tables

VendorTransaction

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
ExcludeVendor

Vendor

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.