Forum Discussion

Byzbkr's avatar
Byzbkr
Frequent Visitor
4 years ago

Changing the datasource

Hello, I have more than one database in my SQL server. In addition, the tables in the databases are same. I created a report with a specific database. However, I want to give the user the option to ...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

Before you invest any more time in that be aware that the Power BI Service and specifically the Power BI gateway does NOT support switching databases.  The difference between SQL.Databases and SQL.Database does only exist in Power BI Desktop.  And we haven't even mentioned the security implications yet - as you would have to give all users full access to all databases on that server to make this work.

  • Byzbkr's avatar
    Byzbkr
    Frequent Visitor
    4 years ago

    Hi, I already using Power BI Destkop and I can give all users full access to all databases. So what should I do for switching databases in visulization-pane?

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      - make sure the data source is in direct query mode 

      - enable the preview feature "dynamic M query parameters

      - learn about how to set up parameters and tie them to filters 

      - modify your M to use custom SQL and include the parameter 

       

      Once again - think long and hard before implementing this.

       

       

       

      • Byzbkr's avatar
        Byzbkr
        Frequent Visitor
        4 years ago

        Thanks for your help. But main problem I can't find a way to modify M query. Is there a similar M query you can share?