How to select the data source with a slicer in the dashboard

[aetapu]

Hi everyone,

I am working on a project where we have an exogenous constraint : we work on a lot of databases.

Schematiquely, I have one postgresql database ("main") with one table ("clients") which is :

client	database_name
1	client1_database_name
2	client2_database_name
...	...
N	clientN_database_name

Each client_database have exactly the same tables, data type etc...

I would like to create a PowerBi report presenting one client_database data, the client_database to extra be a parameter which could be selected with a slicer in the dashboard, after requesting main.clients table to get a list of all the client_database available.

Do you think it is possible ? I am trying without success since this morning. I have the feeling i can't really use a M parameter to chance the datasource with a slicer on a table coming from a DirectQuery request.

I think the answer is related to this : https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters but there is no example with a dynamic list of database possibilities...

Best regards

[v-henryk-mstf]

Hi @aetapu ,

If the data source is connected in dq mode, this requirement may not be realized. If it is an import schema, you can try to create a separate date table. Then link the two data sources to this new date table (and not to each other). Make sure the date format is the same in all three tables.

refer to related link:

Solved: How to mark Date table? - Microsoft Power BI Community

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aetapu]

Hi,

Thanks for your help.

Here are my data model:

 

client_data is my mother table from a database "main_db" (constant) where i find a list of of the database including a table "school".

i have made a parameterized connexion to the database owning "school" :

and I want to be able to slice in the dashboard to update the parameter "db_chosen" and consequently update the data in "school"

In a first try, all my request are in "Import mode" : => i'm not able to bind the paramater "db_chosen" to any data in my model :

So, I understand i have to change my connexion to "school" in DirectQuery mode. I did it :

But, even if the button "Bind to parameter" appeared, i still can't bind it to db_chosen :

("Aucun" = None in French 🙂 )

I don't think i can use a date table because my client database have name like that : "name of client"-"id of the client".

Best regards,