Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Multiple Table.Select Rows Filters in Query

I'm trying to reduce the amount of data I'm receiving from SAP BW (HANA), since I'm working on a stand-alone laptop and it keeps running into memory errors when loading the data from SAP BW (HANA).

 

Below is the current setup I have (simplified). It gives me the data for 2 companies (Company 1 and Company 2). Now I want to add an additional filter on the "Fiscal year.Fiscal year Level 01". Values in that column (when I load it) are "K4/2018", "K4/2019" and "K4/2020". I want to only load 2020-data.

 

I've tried several things (adding another "or" statement, adding another "Table. Select Rows"-line below the first one), but nothing works. Either it doesn't load at all - giving me the error "Unknown error when executing MDX" - or it loads, but doesn't filter on the year.

 

Anyone got a solution?

 

 

let
    Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
    VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
    #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
    #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
        {
            {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
            {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
            {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
            {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
            {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
            {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
            {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
            {Table.SelectRows, each [Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2"}})
in
    #"Toegevoegde items"

 

 

  • Hi Anonymous ,

     

    To my knowledge, you just want to load data in 2020, right? If so, try this:

    let
        Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
        VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
        #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
        #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
            {
                {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
                {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
                {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
                {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
                {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
                {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
                {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
                {Table.SelectRows, each ([Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2") and Text.Contains([Fiscal year.Fiscal year Level 01], "2020")}})    ------------------------added.
    in
        #"Toegevoegde items"

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Hi Anonymous ,

     

    Try the following code:

     

     

    let
        Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
        VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
        #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
        #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
            {
                {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
                {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
                {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
                {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
                {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
                {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
                {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
                {Table.SelectRows, each ([Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2") and ([Fiscal year.Fiscal year Level 01] = "K4/2018" or [Fiscal year.Fiscal year Level 01] = "K4/2019" or [Fiscal year.Fiscal year Level 01] = "K4/2020")}})
    in
        #"Toegevoegde items"

     

     

    Basically I change the select rows to incorporate the columns filter in the same group with parenteses and then within each one have an or statment.

     

    Be aware I may have made some error writing the columns names so please check them.

  • Icey's avatar
    Icey
    Community Support

    Hi Anonymous ,

     

    To my knowledge, you just want to load data in 2020, right? If so, try this:

    let
        Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
        VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
        #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
        #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
            {
                {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
                {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
                {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
                {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
                {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
                {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
                {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
                {Table.SelectRows, each ([Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2") and Text.Contains([Fiscal year.Fiscal year Level 01], "2020")}})    ------------------------added.
    in
        #"Toegevoegde items"

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • fzfz's avatar
    fzfz
    Frequent Visitor

    Hello,

     

    I need to filter measures column and import rows with measure.quantity different to zero.

     

    Any help please ? 

     

    Thank you