Passing Measure output in MQuery

[pbiforum_123]

Instead of Hardcoded 150, Can I use measure? So that user can change whatever the value that they want to pass in the below direct query. Please help..

let
Source = Sql.Database("euwdfas0rssql01.database.windows.net", "EUWDFAS0RSSDB01", [Query="SELECT #(lf) HsCode,#(lf) SUM(SupplementaryUnitQty) AS TotalSupplementaryUnitQty#(lf)FROM #(lf) [dbo].[CBAM_Goods_InScope]#(lf)WHERE #(lf) [EntryIdentifier] IN (#(lf) SELECT [EntryIdentifier]#(lf) FROM [dbo].[CBAM_Goods_InScope]#(lf) GROUP BY [EntryIdentifier]#(lf) HAVING SUM([CustomsValue]) > 500#(lf) )#(lf)GROUP BY #(lf) HsCode;"])
in
Source

[Demert]

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.

[Elena_Kalina]

Hi @pbiforum_123 

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.

[pbiforum_123]

@Elena_Kalina Since it will be passed by the user of the dashboard using the slicer and we cannot use the Parameter in the slicer I want to use the measure the pass the value. So please let me know how to use Measure instead of Parameter.

@Elena_Kalina Hope you have got the requirement. Let me know if you have any questions.

[Elena_Kalina]

Ok, you can try

1. Create a Threshold Bridge Table( create a simple table in Power BI with possible threshold values and add a slicer for users to select values from this table)

2. Create a Measure to Capture Selected Value

Selected Threshold = 
VAR UserSelection = SELECTEDVALUE(Thresholds[Value], 500)
RETURN UserSelection

Modify Your M Query

let
    // Get the threshold value from the model
    ThresholdValue = Text.From(
        Value.NativeQuery(
            #"Your Previous Step", 
            "SELECT [Selected Threshold] AS ThresholdValue"
        ){0}[ThresholdValue], 
        "en-US"
    ),
    
    // Build the dynamic 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

[pbiforum_123]

Anyone having any work around to use the slicer selection instead of parameter in mquery?

[v-dineshya]

HI @pbiforum_123 ,

Thank you for reaching out to the Microsoft Fabric Community forum.

In Power BI, you cannot directly pass a DAX measure value into an M query, because M queries run before DAX measures are evaluated. This is due to the Power BI data refresh and modeling architecture, Power Query is responsible for data load and transformation, while DAX is evaluated after the data is loaded.

Note: You Cannot Use DAX Measures in M because, M queries run at data load time. DAX measures run at report interaction time. There is no direct runtime bridge between DAX measures and M parameters.

Please refer Community thread.

Solved: Use Measure in Mquery - Microsoft Fabric Community

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-dineshya]

Hi @pbiforum_123 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[pbiforum_123]

@Elena_Kalina I have created the table using the below 

CBAM_Value_Parameter = GENERATESERIES(1, 1000, 1)

 I am getting the below error..

Update mquery as per your suggestion..

let
// Get the threshold value from the model
ThresholdValue = Text.From(
Value.NativeQuery(
#"Your Previous Step",
"SELECT [CBAM_Value_Parameter Value] AS ThresholdValue"
){0}[ThresholdValue],
"en-US"
),

// Build the dynamic 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

[Elena_Kalina]

The error you're seeing (Expression.Error: The import "Your Previous Step" matches no exports) occurs because the M query is referencing a step name that doesn’t exist in your Power Query Editor.

When adapting my solution, you’ll need to replace #"Your Previous Step" with the actual name of your previous step in the Power Query sequence.

[pbiforum_123]

@Elena_Kalina Yeah got that but there is no previous step in my case..

[pbiforum_123]

@Elena_Kalina Sorry I am not able to understand what exactly you are trying to say. Can you please explain again.