Forum Discussion

crobson29's avatar
crobson29
Helper I
1 year ago

Filter out entries from DirectQuery tables with a sharepoint list

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?

Need Help

4 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
      crobson29
      Helper I
      1 year ago

      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.

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      crobson29
      Helper I
      1 year ago

      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.