none
SSAS source Query, how to filter with multiple members from a table ? RRS feed

  • Question

  • Hello,

    From a excel worksheet i m able to manage a name range with mulitple values.

    In power Query I m able to load this named range into a table.

         Range= Excel.CurrentWorkbook(){[Name="PLS"]}[Content],
        tfilter = Table.AddColumn(Range, "filter", each "[GRF].[GRF Code].&[" & [Column1]  &"]"),
        Mytable = Table.TransformColumnTypes(tfilter,{{"filter", type text}}),


    Then I create a SSAS query and i would like to filter a dimension members based on my previous table element using a :

        #"Filtered Rows" = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([GRF.GRF Code]) = ......??????

    For one element of my table no issue, i can manage to write sommething like this :

       #"Filtered Rows" = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([GRF.GRF Code]) =  #"Mytable"{0}[filter]  )

    But....how to manage to do it for each of "Mytable" elements acting like a multiple filter upon this dimension attribute?

    any clue is welcome ! :-)

    Regards

    Friday, January 23, 2015 10:56 AM

Answers

  • Hello,

    Thank's to all for you answers.

    for the time beiing I m implementing Bertrand's solution: work OK !

    But of course it's a pity that such multiple operator like "IN" is not implemented as in financial and controling context it's to limited.

    Hope this change will be soon  in the UI....

    Regards

    • Marked as answer by francois_P Thursday, February 5, 2015 10:53 AM
    Thursday, February 5, 2015 10:47 AM

All replies

  • Francois,

    You can provide the members that you want to use in your filter as a list (can be a separate query, named parttypes in the example below) and use a List.Contains statement in the SelectRows statement:

    = Table.SelectRows(#"Added Items", each List.Contains(parttypes, Cube.AttributeMemberId([Inventory Item Assembly.Part Type])))

    Regards,

    Friday, January 23, 2015 12:32 PM
  • thank you.

    BUT STILL a ISSUE as the filter via the list is not done at SERVER Side, but only localy(XL) on the entire result of the cube extract...

    That mean that query folding does not happen...and after this filter the step is no more a "cube"(then you cannot add any members).

    As conclusion: this is not what I need as the filter is not done at server side level.

    Another idea ?


    ==============================

    What i did in detail 

    let

         Range= Excel.CurrentWorkbook(){[Name="PLS"]}[Content],         --get named range with member to filter
         tfilter = Table.AddColumn(Range, "filter", each "[GRF].[GRF Code].&[" & [Column1]  &"]"), --add col to form the mdx members reference to be filtered
         Mytable = Table.TransformColumnTypes(tfilter,{{"filter", type text}}), --set to text

         ListPPI= Table.Column(Mytable, "filter"), --Transform to list

    --SSAS query part
        Source = <...>,
        OLAPDB= <...>,
        OLAPCUBE= <...>,

      #"OLAPPers" = <...>,

       #"Added Items" = Cube.Transform(#"OLAPPers", {{Cube.AddAndExpandDimensionColumn, "[GRF]", {"[GRF].[GRF Code].[GRF Code]"}, {"GRF.GRF Code"}}}), --query

    --Filter :-)

        #"Filtered Rows1" = Table.SelectRows(#"Added Items", each List.Contains(ListPPI, Cube.AttributeMemberId([GRF.GRF Code])))

    in
        #"Filtered Rows1"

    :-)






    • Edited by francois_P Friday, January 23, 2015 2:38 PM
    Friday, January 23, 2015 1:04 PM
  • Hi,

    The only pattern that's currently optimized to member ID filters SSAS is equality against Cube.AttributeMemberId. You'll therefore need to construct a filter that looks like this:

    Table.SelectRows(cube, each Cube.AttributeMemberId([Column]) = "MemberId")

    Composition with "and" and "or" is also supported.

    We could optimize other patterns, but we prioritized the ones that are currently generated by the UI.

    This is not the first time I hear about customers needing to do this. In the future, we may add functionality to dynamically do these kinds of filters, but unfortunately for the moment all the IDs need to be specified in M.

    Hopefully this helps,
    Tristan

    Friday, January 23, 2015 9:53 PM
    Moderator
  • Considering the current limitation of the product explained by Tristan, you could still use a parameter list for the member IDs by including in the code as many comparison statements as may be required.

    If your parameter list Contains less than the max number of values (10 in the code snippet below), you can use a fill-down statement to repeat the last parameter.

    Regards,

    Table.SelectRows(cube, each (
    Cube.AttributeMemberId([Column]) = Paramlist{0} or

    Cube.AttributeMemberId([Column]) = Paramlist{1} or ... Cube.AttributeMemberId([Column]) = Paramlist{9}) ),

    Monday, January 26, 2015 8:52 AM
  • Hello,

    Thank's to all for you answers.

    for the time beiing I m implementing Bertrand's solution: work OK !

    But of course it's a pity that such multiple operator like "IN" is not implemented as in financial and controling context it's to limited.

    Hope this change will be soon  in the UI....

    Regards

    • Marked as answer by francois_P Thursday, February 5, 2015 10:53 AM
    Thursday, February 5, 2015 10:47 AM
  • I finally developped a function to dynamically build the member ID filters expression. This text is then fed to a Expression.Evaluate statement for execution. This way, the query folding is retained. 

    The function takes as inputs a member and the list of member IDs to filter.

    (Cubemember as text, InputList as list) =>
            let
               Transform1= List.Transform( InputList, each "Cube.AttributeMemberId(" & Cubemember &" )="""& _ &"""" ),
               Transform2= "each (" & Text.Range(List.Accumulate(Transform1, "", (state, current) => state & " or " & current),3) & ")"
             in
               Transform2

    Example of use

    let
        Source = OLAP44,
        #"Added Items" = Cube.Transform(Source, {{Cube.AddMeasureColumn, "Headcount", "[Measures].[Headcount]"}, {Cube.AddAndExpandDimensionColumn, "[Snapshot Date]", {"[Snapshot Date].[Month].[Month]"}, {"Snapshot Date.Month"}}}),
        MyList = {"[Snapshot Date].[Month].&[20151001]","[Snapshot Date].[Month].&[20150901]","[Snapshot Date].[Month].&[20151101]"},
        #"Filtered Rows" = Table.SelectRows(#"Added Items", Expression.Evaluate(CubeDimensionContains("[Snapshot Date.Month]",MyList ),#shared) )
    in
        #"Filtered Rows"


    Tuesday, December 8, 2015 3:41 PM
  • Thanks Bertrand, your solution worked wonders for a project I'm working on!
    Tuesday, February 23, 2016 3:33 PM
  • Thanks Bertrand!

    This was a lifesaver. There really should be a way to do this easily in GUI. PowerQuery is such a great tool, but regular users really shouldn't have to use the advanced editor. And they do need a way to keep query folded when combining local and SSAS sources. 

    I was pulling detail data from SSAS tabular (over 5 million possible rows) and needed only 2000 of these. Excel sheet had info on rows that were needed (Product Ids). Query ran 42 minutes before. Now it runs in a second :)


    Friday, September 30, 2016 6:08 AM
  • As of writing this post, Bertrand's solution no longer works for me (PQ version 2.40.4554.161 64-bit).  See below for an example of PQ's new auto generated syntax:

    = Table.SelectRows(#"Filtered Rows", each (Cube.AttributeMemberId([Timekeeper Filters.Timekeeper Number]) = "[Timekeeper Filters].[Timekeeper Number].&[123]" meta [DisplayName = "123"]))

    Note the differences:

    [Timekeeper Filters.Timekeeper Number]
    [Timekeeper Filters].[Timekeeper Number]


    Thanks,
    Simon


    • Edited by Simon Nuss Wednesday, January 25, 2017 2:37 PM
    Wednesday, January 25, 2017 2:37 PM
  • Thank you Bertrand for that resolution. Amazing.

    As Simon pointed out, the new version of Power Query (as of Oct 2017) has a different format. Below is the edit to make his solution work.

    (Cubemember as text, InputList as list) =>
            let
               Transform1= List.Transform( InputList, each "Cube.AttributeMemberId(" & Cubemember &" )="& _ ),
               Transform2= "each (" & Text.Range(List.Accumulate(Transform1, "", (state, current) => state & " or " & current),3) & ")"
             in
               Transform2

    Example of Use

    let
        Source = OLAP44,
        #"Added Items" = Cube.Transform(Source, {{Cube.AddMeasureColumn, "Headcount", "[Measures].[Headcount]"}, {Cube.AddAndExpandDimensionColumn, "[Snapshot Date]", {"[Snapshot Date].[Month].[Month]"}, {"Snapshot Date.Month"}}}),
        MyList = {"""[Snapshot Date].[Month].&[20151001]"" meta [DisplayName = ""20151001""]","""[Snapshot Date].[Month].&[20150901]"" meta [DisplayName = ""20150901""]","""[Snapshot Date].[Month].&[20151101]"" meta [DisplayName = ""20151101""]"},
    
        #"Filtered Rows" = Table.SelectRows(#"Added Items", Expression.Evaluate(CubeDimensionContains("[Snapshot Date.Month]",MyList ),#shared) )
    in
        #"Filtered Rows"


    Asit


    • Edited by AMohanty Tuesday, October 17, 2017 4:42 PM minor edit
    Tuesday, October 17, 2017 4:40 PM