none
Folding Query Back to SSAS Tabular RRS feed

  • Question

  • Hi guys

    I'd like to filter a column based on a list of ~200 items back to the source, in this case it's an SSAS Tabular cube.  I'm a bit unsure how to go about it since I can't find any documentation in the Power Query Formula reference site with respect to cube formulas.

    Below is my script:

    let
        Source = AnalysisServices.Databases("REDACTED", [TypedMeasureColumns = true]),
        #"Staff Utilization and Delinquent Time1" = Source{[Name="Staff Utilization and Delinquent Time"]}[Data],
        #"Staff Utilization and Delinquent Time2" = #"Staff Utilization and Delinquent Time1"{[Id="Staff Utilization and Delinquent Time"]}[Data],
        #"Staff Utilization and Delinquent Time3" = #"Staff Utilization and Delinquent Time2"{[Id="Staff Utilization and Delinquent Time"]}[Data],
        #"Added Items" = Cube.Transform(#"Staff Utilization and Delinquent Time3", {{Cube.AddAndExpandDimensionColumn, "[Period Filters]", {"[Period Filters].[Start Date].[Start Date]"}, {"Period Filters.Start Date"}}, {Cube.AddAndExpandDimensionColumn, "[Timekeeper Filters]", {"[Timekeeper Filters].[Timekeeper Number].[Timekeeper Number]"}, {"Timekeeper Filters.Timekeeper Number"}}, {Cube.AddMeasureColumn, "Utilization", "[Measures].[Utilization]"}}),
        #"Filtered Rows" = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([Period Filters.Start Date]) = "[Period Filters].[Start Date].&[" & Date.ToText(Date.AddDays(Date,-7), "YYYY-MM-DD") & "T00:00:00]" meta [DisplayName = "6/3/2012"]),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Cube.AttributeMemberId([Timekeeper Filters.Timekeeper Number]) = "[Timekeeper Filters].[Timekeeper Number].&[2180]" meta [DisplayName = "2180"] or Cube.AttributeMemberId([Timekeeper Filters.Timekeeper Number]) = "[Timekeeper Filters].[Timekeeper Number].&[2281]" meta [DisplayName = "2281"])
    in
        #"Filtered Rows1"

    Essentially I'd like to change #"Filtered Rows1" so, instead of filtering by the hard coded numbers (2180 and 2281, in this example), it will filter by a list of ~200 numbers stored in the local Excel workbook which the user inputs.

    Suggestions?

    Thanks,
    Simon

    Tuesday, January 24, 2017 10:03 PM

Answers

All replies