Update Parameter with the value in the Measure

[svsantosh]

I have a Power Query that calls a PLSQL function and passes some arguments. This PLSQL function returns a RefCursor that we have linked with a Visual, so that we can see the resulting rows on the screen.

I have created a new Parameter, and used it in the Power Query at the place of Argument, as follows:

let
Source = Odbc.Query("dsn=XXX", "DECLARE P_RC SYS_REFCURSOR;#(lf)#(lf)BEGIN#(lf)#(lf)P_RC := GET_CASE_DETAILS("&CID&");#(lf)#(lf)DBMS_SQL.RETURN_RESULT(P_RC);#(lf)#(lf)END;")
in
Source

The PLSQL Block also looks like this:

DECLARE
   P_RC SYS_REFCURSOR;
BEGIN
   P_RC := GET_CASE_DETAILS("<<JSON>>");
   DBMS_SQL.RETURN_RESULT(P_RC);
END

Here CID is the Parameter.

Our requirement is:

We have a set of slicers in a page, where users can select some values. We have created a Measure that collects all these values and concatenates to created a JSON text. Now we want to set this JSON text (that is in the Measure) to the Parameter "CID", so that the Power Query can refresh the Dataset, and we display the latest set of results in the Table Visual.

Botton line is, I want to update the Parameter with the value calculated inside the Measure.

Please help.

[svsantosh]

Hi Speedramps,

I will try this solution. Sounds like it may work, but need to see.

Thanks.

[v-angzheng-msft]

Hi, @svsantosh 

May I ask if your problem has been solved?  Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you.  Thank you.

It would be better if you could post the solution here which will benefit other users who might have the same problem.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

Please consider this solution and like the thumbs up button

Try using a Power App imbeeded in the Power Bi report page, so the user can select some values and write them to a parameter table.  Then include the table in the refesh.

You can also make that table a direct query, and instantly update the report without a refesh but you need a data source that supports Power App idirect query ... for example Azure SQL.

There are plenty of youtube guides on how to do this.