Passing Measure output in MQuery

Hi Anonymous 

Yes, you can try to replace the hardcoded 500 value with a dynamic measure from your Power BI model.

Since you're working with a DirectQuery, we need to use Power BI's parameters to pass values from your report to the SQL query.

I) Create a Parameter

1. In Power BI Desktop, go to Home tab → Manage Parameters → New Parameter

2. Create a parameter named CustomsValueThreshold with:

   • Type: Decimal Number

   • Suggested Values: List of values or Any value

   • Default value: 500 (or whatever you prefer)

II) Modify your query

let
    ThresholdValue = Text.From(Parameter1), // Convert parameter to text for SQL
    Source = Sql.Database(
        "euwdfas0rssql01.database.windows.net", 
        "EUWDFAS0RSSDB01", 
        [Query="
        SELECT 
            HsCode,
            SUM(SupplementaryUnitQty) AS TotalSupplementaryUnitQty
        FROM 
            [dbo].[CBAM_Goods_InScope]
        WHERE 
            [EntryIdentifier] IN (
                SELECT [EntryIdentifier]
                FROM [dbo].[CBAM_Goods_InScope]
                GROUP BY [EntryIdentifier]
                HAVING SUM([CustomsValue]) > " & ThresholdValue & "
            )
        GROUP BY 
            HsCode;"
        ])
in
    Source

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

Hi, A PowerBI User can never return a input field / parameter from a PowerBI report back to the data load / Power Query M steps.  What I would suggest if possible to remove the HAVING SUM([CustomsValue]) > 500 code in your sql statement. So you load all records. When it's all loaded you can write a measure to only show records in a table visual that have a customervalue above x. And X you can return out of the slicer value the user selected.