parameter controlled by slicer

[richard_wylde]

Hello,

In Power Query I have a query which is successfully sourced from the following, where Parameter1 is currently set to '[Sales]'

= Sql.Database("SERVERNAME", "DBNAME", [Query="SELECT  [Client]#(lf)      ,sum(" & Parameter1 & ")  #(lf)  FROM [T Sales]#(lf)  group by [Client] "])

However I now wish to link Parameter1 to a Slicer containing  [Sales] and [Permium] , so as the query returns either Sales or Premium depending on which the user selects, is this possible ??

[v-lid-msft]

Hi @richard_wylde ,

If you are using the Import mode, even the parameter changed, it need to refresh the dataset to get the result based on the parameter. When using Import mode, it better to store all the rows based on all the value of slicer, then the report will be filtered by the slicer.

If you want to get the part of data based on the slicer, the fact table should be DirectQuery mode, then when the slicer changed, it will query different result based on it withough using parameter, please refer to this document: https://community.powerbi.com/t5/Power-Query/Using-parametre-in-url-rp/td-p/1084291

Best regards,

[richard_wylde]

Thanks @v-lid-msft , I wasn't aware of query string parameters, that's great. It appears this can filter the number of records, but I'm trying to filter the number of columns, so as to give the user a choice of columns to choice from in the report, would there be a way to do this ?

[v-lid-msft]

Hi @richard_wylde ,

The key is the storage mode of fact table, if the fact table is in Direct Query Mode, it will retrive data based on the slicers, filters,interactor,  or url string parameter(actually it is kind of page filter), so it is will automatically send query like select ... from ... where column A = ... and column B = ... , based on the silcers and filter user select.

But if the fact table is in import mode, the data will only refreshed when execute an on-demand refresh or scheduled refresh, it will also filter data by slicer or filter if you import all the data in (mean no query parameter applied in the query), it will not query the source to get newest data when slicer or filter changed.

So in conclusion, if you want to get the latest data and do not want to import all the data, you can try to put the fact table in Direct Query Mode, please refer to this document: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

Best regards,

[nandukrishnavs]

@richard_wylde 

You cannot change the EditQuery parameters from the report.  You could bring sales and premium into Power BI and write a DAX measure to switch the column based on slicer selection.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[richard_wylde]

Thanks for letting me know, I shall suggest it as a new feature

[parry2k]

@richard_wylde that is always good, post ideas on idea forum but recommended to check if there is an existing idea and vote for that, more votes, more attention to the idea.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[richard_wylde]

Thanks @parry2k , do you know if there is another way I could chop up the Select statement ?

[parry2k]

@richard_wylde at this point, you cannot pass slicer values to a power query parameter. 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!