Forum Discussion

Signore_Ands's avatar
Signore_Ands
Advocate III
4 years ago
Solved

Create Parameter (Lookup) for Server from a Country/Region Parameter

In Power BI Desktop (Not service):

What I need:

1. Select a reporting region from a defined list

2. Lookup the corresponding server for that reporting region and then...

3. Use that server in the main transactional query 

 

I have already created a lookup table that has the Region Names and the corresponding servername.

 

I've created the parameter for the region but I am struggling with the step of creating a parameter for the server name.

None of the threads that I have found seem to cover what I am looking to do.

How do I do this?

Need Help
  • Signore_Ands's avatar
    Signore_Ands
    4 years ago

    Thanks for the reply, Eason.
    Yes, it's probably a cascading parameter that would be needed. 
    My report needs a specific server name for the transaction data but supporting data is kept in sharepoint tables for which I need a region name to filter the data.
    I've managed to get it to work by having a parameter for the region name and a query that reads from a server list, using the region name as a filter.  It's a little bit long winded, but it does work.

    The trick was getting the query - from the list of servers - to produce a single value.
    This step in the server_name query does that:
    = FilteredRows{0}[Server]

    Then for the transaction query the servername is replaced by #"Server_Name" to pick up the result of the Server_Name query.


2 Replies

    • Signore_Ands's avatar
      Signore_Ands
      Advocate III
      4 years ago

      Thanks for the reply, Eason.
      Yes, it's probably a cascading parameter that would be needed. 
      My report needs a specific server name for the transaction data but supporting data is kept in sharepoint tables for which I need a region name to filter the data.
      I've managed to get it to work by having a parameter for the region name and a query that reads from a server list, using the region name as a filter.  It's a little bit long winded, but it does work.

      The trick was getting the query - from the list of servers - to produce a single value.
      This step in the server_name query does that:
      = FilteredRows{0}[Server]

      Then for the transaction query the servername is replaced by #"Server_Name" to pick up the result of the Server_Name query.