Filter out entries from DirectQuery tables with a sharepoint list

[crobson29]

I have been asked to figure out a way to filter out certain sales orders from a large, company wide dataset if they are added to a sharepoint list/excel document.  The dataset is a Power BI Semantic model that we use in a lot of reports, but I am not the owner and cannot make changes to the actual dataset.  We have gotten around this when we needed to add more data in the past by creating a directquery local model and adding in the data that we needed through import.  This works OK for adding missing data, but given the limited relationships between directquery and import tables, I can't figure out a way to filter OUT the sales orders that are present in a table.  The RELATED function doesn't work over limited relationships, and I have seen that the LOOKUPVALUE function works, but it doesn't seem to be working for me.  Any ideas?

[johnt75]

You could create a calculation group with a calculation item that filters out the relevant sales,e.g.

Filter Sales =
CALCULATE (
    SELECTEDMEASURE (),
    NOT 'Remote Sales'[Sale ID] IN DISTINCT ( 'Excel Filter'[Sale ID] )
)

You could then apply this as a page or report level filter.

[crobson29]

This seems promising, as it did not result in an error, but the filter does not seem to do anything when applied.  I have not played around with calculation groups before, I will look into this more.

[Bibiano_Geraldo]

Hi @crobson29 ,

You can use DAX measure to calculate the sales amount only if the current OrderID does not exist in the ExcludedSalesOrders table, something like this:

SalesAmountFiltered = 
IF(
    ISBLANK(
        LOOKUPVALUE(
            ExcludedSalesOrders[OrderID], 
            ExcludedSalesOrders[OrderID], 
            'SalesOrders'[OrderID]
        )
    ),
    SUM('SalesOrders'[SalesAmount]), 
    BLANK()
)

[crobson29]

I believe this would work if I were trying to get the total sales amount, but I am trying to filter out sales documents from showing up in Matrix visuals and tables.