Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Passing Measure output in MQuery

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...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Elena_Kalina
Solution Sage
1 year ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Elena_Kalina
      Solution Sage
      1 year ago

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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