Formula.Firewall error with function and the same data source

[mohammadjavaher]

Hi.

I'm using Power BI Desktop Jan 2023. I have two queries. One is a function:

()=>
let
    Source = Sql.Database("1.2.3.4", "AXDB"),
    dbo_INVENTCLOSING = Source{[Schema="dbo",Item="INVENTCLOSING"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_INVENTCLOSING, each ([ACTIVE] = 1) and ([ADJUSTMENTTYPE] = 2) and ([CANCELLATION] = 0) and ([INVENTCOSTSTATUS] = 0) and ([STOPRUNNING] = 0)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"TRANSDATE", type date}}),
    #"TransDate" = #"Changed Type"[TRANSDATE],
    TransDate1 = List.Max(#"TransDate")
in
    TransDate1

The other is a query in which this function is used:

let
    Source = Sql.Database("1.2.3.4", "AXDB"),
    dbo_CUSTINVOICETRANS = Source{[Schema="dbo",Item="CUSTINVOICETRANS"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CUSTINVOICETRANS,{"INVENTTRANSID", "INVOICEDATE", "ITEMID", "LINEAMOUNT"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"INVOICEDATE", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [INVOICEDATE] <= LastClosing()),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"INVENTTRANSID"}, INVENTTRANSORIGIN, {"INVENTTRANSID"}, "INVENTTRANSORIGIN", JoinKind.Inner),
    #"Expanded INVENTTRANSORIGIN" = Table.ExpandTableColumn(#"Merged Queries", "INVENTTRANSORIGIN", {"SALESID", "CostAmount"}, {"SALESID", "CostAmount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded INVENTTRANSORIGIN",{"INVENTTRANSID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Margin", each [LINEAMOUNT] + [CostAmount]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Margin", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"CostAmount"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"SALESID", "ITEMID", "INVOICEDATE", "LINEAMOUNT", "Margin"})
in
    #"Reordered Columns"

In this query, when I reach "Merged Queries" step, I receive this error:

Formula.Firewall: Query 'CUSTINVOICETRANS' (step 'Merged Queries') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

When I move "Filtered Rows" to last step, I receive the (almost) same error on last step (Not on "Merged Queries" step). This is the modified query:

let
    Source = Sql.Database("1.2.3.4", "AXDB"),
    dbo_CUSTINVOICETRANS = Source{[Schema="dbo",Item="CUSTINVOICETRANS"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CUSTINVOICETRANS,{"INVENTTRANSID", "INVOICEDATE", "ITEMID", "LINEAMOUNT"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"INVOICEDATE", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"INVENTTRANSID"}, INVENTTRANSORIGIN, {"INVENTTRANSID"}, "INVENTTRANSORIGIN", JoinKind.Inner),
    #"Expanded INVENTTRANSORIGIN" = Table.ExpandTableColumn(#"Merged Queries", "INVENTTRANSORIGIN", {"SALESID", "CostAmount"}, {"SALESID", "CostAmount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded INVENTTRANSORIGIN",{"INVENTTRANSID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Margin", each [LINEAMOUNT] + [CostAmount]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Margin", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"CostAmount"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"SALESID", "ITEMID", "INVOICEDATE", "LINEAMOUNT", "Margin"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [INVOICEDATE] <= LastClosing())
in
    #"Filtered Rows"

And this is the error:

Formula.Firewall: Query 'CUSTINVOICETRANS' (step 'Filtered Rows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

All of my queries come from a single data source (D365FFO). I have done the same thing in another report with another database (and that report contains more complex queries using the same function, compared to the above). What's wrong?

Thanks.