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.
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.
Elena_Kalina Yeah got that but there is no previous step in my case..
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.Hi Anonymous ,
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.
