cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lowell New Member
New Member

parameterize connection

I'm trying to get used to Power BI Desktop, and seem to need a little boost either getting started, or understanding the limitations. 

I want to build a sample dashboard, and what better way to get started than have a dashboard bubble up info I am familiar with, right?
But Right away, since I manage multiple servers, I need to be able to change the server name from a drop down menu, and have the queries refresh.
If I could get just one dang query to do that, i could run with the ball from there.
I found an article that kind of shows how to get a connection to use a variable, but it's not working for me so far. I get confused about why i HAVE to use excel, instead of a query or static list, and the half dozen iterations I've tried never worked. I get killed in DAX/BI syntax.

I want a query available that shows me all my servers from central Management Server, easy enough right?

 

SELECT DISTINCT name,server_name FROM msdb.[dbo].[sysmanagement_shared_registered_servers]

 

it could just as well be a select from a static list, ie SELECT'(local) AS name UNION ALL SELECT '(local)\SQLEXPRESS', but it needs to be a refreshible query to occasionally get the new server list.

So i can get that into a PowerBI Desktop data, but i'm not seeing how to get something to assign to a variable, so that i could then use that variable to modify a connection string.
so say i wanted to just run the most basic query, something like this:


SELECT 
   SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,
   SERVERPROPERTY('Edition')       AS Edition,
    s.cpu_count ,
    s.hyperthread_ratio ,
    s.cpu_count / s.hyperthread_ratio As NumberofCores,
    s.physical_memory_kb/1024 As MemoryinMb,
    s.virtual_machine_type_desc,
    CASE WHEN s.virtual_machine_type > 0 THEN 'Virtual' ELSE '' END As IsVirtual--1 = hypervisor, 2 = other type of virtual
   FROM  sys.dm_os_sys_info s


if i got that to work, i could then use the same logic on other queries, and have dozens of other objects in a dashboard that pulls various details a DBA would care about.
there is a brief article on Power BI Tutorial: How to Parameterize Connection Variables in Power Query at
https://businessintelligist.com/2015/05/12/power-bi-tutorial-how-to-parameterize-connection-variable..., but i need a better leg up, than that two year old article.
Has anyone ever done what I'm asking? can you get a drop down selection to assign a value to a variable, so that connection strings would refresh?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: parameterize connection

Highlighted
Community Support Team
Community Support Team

Re: parameterize connection

@Lowell,

 

See link below for choosing Query in the Suggested Values dropdown box.

https://blog.crossjoin.co.uk/2016/08/30/data-driven-power-bi-desktop-parameters-using-list-queries/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User
Super User

Re: parameterize connection

Highlighted
Community Support Team
Community Support Team

Re: parameterize connection

@Lowell,

 

See link below for choosing Query in the Suggested Values dropdown box.

https://blog.crossjoin.co.uk/2016/08/30/data-driven-power-bi-desktop-parameters-using-list-queries/

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