cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Filtering a list with another list then collapsing columns back into SSAS

Hi all,

 

Not sure if possible. I want to filter a list of "account numbers" from an SSAS query using another list I have. Here is what i have so far:

 

let
    Source = AnalysisServices.Databases("bi-livecubes", [TypedMeasureColumns=true, Implementation="2.0"]),
    Cubes = Source{[Name="Cubes"]}[Data],
    LoanServicing1 = Cubes{[Id="LoanServicing"]}[Data],
    LoanServicing2 = LoanServicing1{[Id="LoanServicing"]}[Data],
    #"Added Items" = Cube.Transform(LoanServicing2,
        {
            {Cube.AddAndExpandDimensionColumn, "[Account]", {"[Account].[Account Number].[Account Number]"}, {"Account.Account Number"}}
        }),
    
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each List.ContainsAny(Table2,{[Account.Account Number]})),
    #"Collapsed and Removed Columns" = Cube.CollapseAndRemoveColumns(#"Filtered Rows", {"Account.Account Number"})
in
    #"Collapsed and Removed Columns"

 

Get an error saying "The value is not a cube". Is there a way of doing this? if i was to filter to a single value using text.contains it would allow me to collapse columns?

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@gp93 

I am not familiar with CUBEs in PQ but 
Filter a column based on another list will go like:

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@gp93 

I am not familiar with CUBEs in PQ but 
Filter a column based on another list will go like:

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Hi Fowmy,

 

Thanks for getting back to me, you have solved it! Very strange - List.ContainsAny was working to filter the list in the previous step but when i changed it to your method it now allows me to collapse the columns!

 

I will mark your post as the solution but would appreciate if anybody knows the reason for this to let me know just for my knowledge.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors