Filtering Data In AnalysisServices.Database Source

[Vannikannan04]

Hi All,

I am using AnalysisServices.Database (Azure Analysis Services) as a source in Power BI. I want to filter the data before loading it into Power BI, but currently the filtering only works after expanding the cube in Power Query.

I tried creating parameters and passing them into the query, but it still didn’t apply the filter at the source level. Request you to let me know if there is any way to achieve it.

Here’s a snippet of my Power Query code:

#"Profit and Loss1" = Source{[Id="Model"]}[Data],
#"Profit and Loss2" = #"Profit and Loss1"{[Id="Model"]}[Data],
#"Added Items" = Cube.Transform(#"Profit and Loss2",
{
{Cube.AddAndExpandDimensionColumn, "[- Currency]", {"[- Currency].[Currency].[Currency]"}, {"- Currency.Currency"}},
{Cube.AddAndExpandDimensionColumn, "[- Scenario]", {"[- Scenario].[Scenario].[Scenario]"}, {"- Scenario.Scenario"}},
{Cube.AddAndExpandDimensionColumn, "[Measure - PnL GMVA]", {"[Measure - PnL GMVA].[PnL GMVA Level 1].[PnL GMVA Level 1]", "[Measure - PnL GMVA].[PnL GMVA Level 2].[PnL GMVA Level 2]"}, {"Measure - PnL GMVA.PnL GMVA Level 1", "Measure - PnL GMVA.PnL GMVA Level 2"}},
{Cube.AddAndExpandDimensionColumn, "[ORG - Business Unit]", {"[ORG - Business Unit].[Business Unit].[Business Unit]"}, {"ORG - Business Unit.Business Unit"}},
{Cube.AddAndExpandDimensionColumn, "[- Time]", {"[- Time].[Half Year].[Half Year]", "[- Time].[Month].[Month]", "[- Time].[Quarter].[Quarter]", "[- Time].[Year].[Year]"}, {"- Time.Half Year", "- Time.Month", "- Time.Quarter", "- Time.Year"}},
{Cube.AddAndExpandDimensionColumn, "[Product]", {"[Product].[SKU Code].[SKU Code]"}, {"Product.SKU Code"}}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each
Cube.AttributeMemberId([#"- Scenario.Scenario"]) = "[- Scenario].[Scenario].&[CY Discrete Actuals [CYD]]" and
Cube.AttributeMemberId([#"- Currency.Currency"]) = "[- Currency].[Currency].&[CONSTANT_EURO_2025]"
)

[v-ssriganesh]

Hello @Vannikannan04,  

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @Vannikannan04,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Vannikannan04,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @Vannikannan04,
Thank you for reaching out to the Microsoft fabric community forum.

To filter data at the source level from your Azure Analysis Services cube in Power BI, you can use Cube.ApplyParameter in Power Query to apply filters before expanding the cube, which enables query folding and improves performance. Instead of filtering after expanding dimensions, add the scenario and currency filters directly within the Cube.Transform step.

Check your filter values are correctly specified using the cube’s unique member names. To confirm query folding, right-click the transform step in Power Query Editor and check "View Native Query" to see if the MDX query includes your filters.

For dynamic filtering, create Power Query parameters for the scenario and currency values and reference them in the Cube.ApplyParameter function. If you encounter issues like invalid member names or no data returned, double-check the cube’s hierarchy structure or share any error messages for further assistance.

 
Best regards,
Ganesh Singamshetty.