Forum Discussion
Passing Measure output in MQuery
v-dineshya Thanks for checking. We have done it in other way. I will share it once it is done.
Right now it is required we can close this.
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
In Power BI Desktop, go to Home tab → Manage Parameters → New Parameter
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.
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.
Ok, you can try
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)
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 SourceElena_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
SourceThe 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.
Anyone having any work around to use the slicer selection instead of parameter in mquery?
HI Anonymous ,
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.
