Forum Discussion

raji's avatar
raji
Regular Visitor
9 years ago

user parameter in dynamic query

Hello!   I'm trying to pass the value of a parameter to my dynamic query statement so that I can limit the dataset on the source db (dealing with extremely large data).   I tried the following......
Need Help
v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
9 years ago

Hi raji,

Parameter is supported under Direct Query. Check:

Deep Dive into Query Parameters and Power BI Templates

This error “Mixing DirectQuery and imported data is not supported." Might be caused by the Parameter status, please right click the parameter, and uncheck the Enable Load option (If checked, after click close and apply, Power BI desktop would generate the error "Mixing DirectQuery and imported data is not supported."):

In addition, please take a notice of the data type of the data source and the parameter, make sure the type matches.

Please reply back if you need any further assistance on this.

Regards

raji's avatar
raji
Regular Visitor
9 years ago

Sorry for the delay - I was on business travel and wasn't able to check this... back now...

 

Looked at the link and I think I see the issue/disconnect now... I was looking for a query parameter that the user could enter and I could pass to the select statement to limit the rows returned (vs a slicer and filtering on it since the dataset is millions of records).  However, using the link to query params and templates provided above, it looks like this is only possible using Desktop BI vs published reports that users are using.

 

If I misunderstood and there is a way to do this where the users can enter the parameter value from the report, would love to hear!

 

Thanks all for your feedback!!

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    Hi raji,

     

    Currently I don't think this could be done under Report level with users.

    Query Parameter is available ehn query the data, not after the query. If we would like to filter the report, we could add the slicer Visual. Which user could be able to select in Reports:

    slicers in Power BI

    Regards

    • ozmike's avatar
      ozmike
      Resolver I
      9 years ago

       

      Here is an example using a HANA database , with working syntax

      enjoy.  Note you must use the internal SAP hana column names , the column displayed in PBI  are "labels" and may have spaces in them or be completely different to the actual sap columns

       

       

      let
xx = "0000610127",
sel = "select ""ColumnName"" from ""ZXXX.XXX::TABLE""  where ""ColumnName"" = '"  & xx & "'  ",
Source = SapHana.Database("Server:12345", [Query=sel])
in
Source

       

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi raji

     

    Are you able to provide your Parameter Name, as well as a sample or the actual query?

     

    I have done this in the past and it a process of making sure you have got your parameter names and Power Query (M) syntax correct.

    • ozmike's avatar
      ozmike
      Resolver I
      9 years ago

      Sorry not sure what you are asking?