Forum Discussion
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
- MFelixSuper User
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.
- IceyCommunity 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.
- fzfzFrequent Visitor
Hello,
I need to filter measures column and import rows with measure.quantity different to zero.
Any help please ?
Thank you