Forum Discussion
Injecting a Dynamic Variable into an Analysis Services Query for Filtering Analysis Serv. Cube Data
Hello Fabric Community,
I'm currently working with Analysis Services to query an anaylsis services cube and need some help with injecting a dynamic variable into the middle of my query. I have a weekly-updated list of budget codes that I want to use to filter my data, but I haven't been able to figure out how to properly incorporate this variable.
Below is an example of the query I'm working with the following DAX:
EVALUATE
// We want to get Var BC's from a different query
VAR BudgetCodes = {"TXP3GA182632","TXP3HQ01","TXP3HQ01D","TXP3HQ01GC","TXP3HQ01SPC"}
RETURN
SUMMARIZECOLUMNS(
'Dim PO Budget Code'[Budget Code],
'Dim Cost Center'[Cost Center Name],
'Dim Invoice Link'[Invoice Link],
KEEPFILTERS( TREATAS( BudgetCodes, 'Dim PO Budget Code'[Budget Code] )),
KEEPFILTERS( FILTER( ALL( 'Dim Cost Center'[Cost Center Code] ), NOT( ISBLANK( 'Dim Cost Center'[Cost Center Code] )))),
KEEPFILTERS( FILTER( ALL( 'Dim PO Budget Code'[Budget Code] ), 'Dim PO Budget Code'[Budget Code] <> "0" )),
"PO Ordered Amount Excluding Tax USD", [PO Ordered Amount Excluding Tax USD]
)
ORDER BY
'Dim PO Budget Code'[Budget Code] ASC,
'Dim Cost Center'[Cost Center Name] ASC,
'Dim Invoice Link'[Invoice Link] ASC
Thanks for sharing the update. Since your report uses DirectQuery, you can make budget code filtering dynamic without hardcoding it in your DAX query.
1.Use a slicer linked to a budget codes table to filter the data dynamically.
2. If needed, set up Row-Level Security in SSAS to restrict data based on user selection.
For an advanced option, you can try Dynamic M Query Parameters. This lets you filter directly in Power Query with parameters. Just make sure the Dynamic M Query Parameters feature is enabled under Options → Preview Features, and it's properly linked to your budget codes.
FYI:
For more details, please refer to the following documentation.
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Connect to Analysis Services data in Power BI Desktop - Power BI | Microsoft Learn
I hope this helps...
8 Replies
Dynamic M Query Parameters are only available for Direct Query data sources, not for live connections.
In this case, we are using a DirectQuery. Not a live connection
Hello leina_daniel ,
Thanks for posting your query in the Microsoft Fabric Community. The approach to dynamically filtering budget codes depends on your connection mode in Power BI1. Dynamic M Query Parameters work only in DirectQuery mode.
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn2. Dynamic M Query Parameters are NOT supported Live Connection
Instead, use slicers linked to a budget codes table or configure RLS in SSAS for filtering.
Connect to Analysis Services data in Power BI Desktop - Power BI | Microsoft Learn
Thanks for your continuous inputs for the community lbendlin .
To provide the most accurate solution, could you confirm whether your report is using DirectQuery, or a Live Connection?
Regards,
Yugandhar.
The report uses a DirectQuery connection. Apologies for not clarifying sooner!
Thanks for sharing the update. Since your report uses DirectQuery, you can make budget code filtering dynamic without hardcoding it in your DAX query.
1.Use a slicer linked to a budget codes table to filter the data dynamically.
2. If needed, set up Row-Level Security in SSAS to restrict data based on user selection.
For an advanced option, you can try Dynamic M Query Parameters. This lets you filter directly in Power Query with parameters. Just make sure the Dynamic M Query Parameters feature is enabled under Options → Preview Features, and it's properly linked to your budget codes.
FYI:
For more details, please refer to the following documentation.
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Connect to Analysis Services data in Power BI Desktop - Power BI | Microsoft Learn
I hope this helps...
Hi leina_daniel ,
We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.
Your feedback is valuable to us, and we look forward to hearing from you soon.
Thanks..
Hi leina_daniel ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi leina_daniel ,
Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.
