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 ...
Need Help
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.