Querying Semantic Model into Excel using parameters

[la-142724]

I have an excel model where I need to pass the parameter guests per ambassador.

VAR __DS0FilterTable3 = 

TREATAS({20}, 'AMBASSADORS PER GUESTS'[AMBASSADORS PER GUESTS])

Right now it is hard coded at {20}, but is there a way to reference a cell in excel and pass this value in query?

Full connection below:

DEFINE

VAR __DS0FilterTable = 

TREATAS({"AMS"}, 'AIR SEGS DATABRICKS-AMS'[Connection])

/*VAR __DS0FilterTable2 = 

TREATAS(

{"91-120",

"121-150",

"151-180",

"181-210",

"211-240",

"241-270",

"271-300",

"301-330"},

'CONNECTION RANGE'[Connection Range]

)*/

VAR __DS0FilterTable3 = 

TREATAS({20}, 'AMBASSADORS PER GUESTS'[AMBASSADORS PER GUESTS])

VAR __DS0FilterTable4 = 

TREATAS({"AF",

"AZ",

"DL",

"KL",

"VS"}, 'AIR SEGS DATABRICKS-AMS'[TM_CARRIER])

VAR __DS0FilterTable5 = 

FILTER(

KEEPFILTERS(VALUES('AIR SEGS DATABRICKS-AMS'[HOUR])),

AND('AIR SEGS DATABRICKS-AMS'[HOUR] >= 6, 'AIR SEGS DATABRICKS-AMS'[HOUR] <= 13)

)

VAR __DS0FilterTable6 = 

TREATAS({2024}, 'CALENDAR'[Year])

VAR __DS0Core = 

SUMMARIZECOLUMNS(

'AIR SEGS DATABRICKS-AMS'[MONTH],

'AIR SEGS DATABRICKS-AMS'[HOUR],

'AIR SEGS DATABRICKS-AMS'[DAY],

__DS0FilterTable,

/*__DS0FilterTable2,*/

__DS0FilterTable3,

__DS0FilterTable4,

__DS0FilterTable5,

__DS0FilterTable6,

"ALL GUESTS",'AIR SEGS DATABRICKS-AMS'[AIR GUESTS],

"GUESTS_PER_FLIGHT", 'AIR SEGS DATABRICKS-AMS'[GUESTS PER FLIGHT],

"DISTINCT_FLIGHTS", 'AIR SEGS DATABRICKS-AMS'[DISTINCT FLIGHTS],

"AMBASSADORS", 'AIR SEGS DATABRICKS-AMS'[AMBASSADORS]

)

VAR __DS0BodyLimited = 

TOPN(

500000,

__DS0Core,

'AIR SEGS DATABRICKS-AMS'[MONTH],

1,

'AIR SEGS DATABRICKS-AMS'[HOUR],

1,

'AIR SEGS DATABRICKS-AMS'[DAY],

1

)

EVALUATE

__DS0BodyLimited

ORDER BY

'AIR SEGS DATABRICKS-AMS'[MONTH],

'AIR SEGS DATABRICKS-AMS'[HOUR],

'AIR SEGS DATABRICKS-AMS'[DAY]

[v-yifanw-msft]

Hi @la-142724 ,

Unfortunately, Power Query does not directly support passing dynamic parameters from Excel to Power BI Desktop queries. The "Analyze in Excel" feature may be suitable for your needs and you can follow the steps below:

1. Create the measures that reference the parameters directly in Power BI Desktop and publish the Power BI Desktop file to Power BI service.
2. In Power BI service, use the "Analyze in Excel" feature to open the dataset in Excel. This allows you to interact with Power BI datasets directly from Excel. See the following documentation for more information:
   Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn
3. At this point, adjust the value of the parameter in the Power BI service so that when you refresh the data in Excel through the "Analyze in Excel" connection, the value will be reflected.

Note that this method does not allow real-time parameter changes directly from Excel.

Best Regards,

Ada Wang

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