Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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?

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    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

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.