Filter DAX import query using a List vs Formula.Firewall error

[puggaard]

Hi, 

I'm trying to do a DAX Import query filtered by a list. The list, tblSKUlist, comes from an Excel table, and the file is stored in Sharepoint / Teams.

My challenge is the Formula.Firewall error. I'd rather not change the privacy settings. (everything works locally when I do so, but I also need to make it work on the service)

Any suggestions?

let
    Source = AnalysisServices.Database(
    "cube.mycompany.org",
    "sales",
    [
        Query = " EVALUATE SUMMARIZECOLUMNS (
            Customer[Account No],
            Customer[Account],
            'Product'[SKU No],
            'Product'[SKU],
            'Order'[Order No],    

        FILTER ('Product', 'Product'[SKU No] = """ & Text.Combine ( tblSKUlist, """||'Product'[SKU No] =""" ) & """
        ),
    ""[Invoiced Qty]"", [Invoiced Qty],
    ""[Net Sales]"", [Net Sales]
            )",
        Implementation = "2.0"
    ]
),

KR, Peter

[puggaard]

Hi lbendlin, it took some attempts to make it work, but the last part of the article made it! Thanks!

KR, Peter

[lbendlin · Accepted solution]

Retrieve the Excel file contents in the same partition (query) rather than referencing it across partitions.

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn