cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lowell
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 III
Super User III

Re: parameterize connection

Highlighted
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: parameterize connection

Highlighted
Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors